// tasks/scheduler.js
const androidPublisherConn = require("../google/googleapis"); // './publisher' 是该文件的路径
//导入dao
const db = require("../pool.js");
const utils = require("../utils/index.js");
// const puppeteer = require('puppeteer');
// 引入uuid模块
const { v4: uuidv4 } = require("uuid");
const pool = require("../pool.js");

const AppCommentDao = require("../dao/AppCommentDao");
const cron = require("node-cron");
const moment = require("moment");

const express = require("express");
const router = express.Router();
const path = require("path");
const utilsServer = require("../utils/serverInfo.js");
const momentx = require("moment-timezone");
const multer = require("multer");
const AdmZip = require("adm-zip");
const AppInfoParser = require("app-info-parser");
const plist = require("plist");
const fs = require("fs");
const { readManifest } = require("node-aab-parser");
const msgUtils = require("../utils/feishu.js");
const crypto = require("crypto");
const CryptoJS = require("crypto-js");
const FormData = require("form-data");
const { exec } = require("child_process");
const config = require("../config");
const axios = require("axios");
const jwt = require("jsonwebtoken");
const jwksClient = require('jwks-rsa');
const xlsx = require('xlsx');
// 定义中国时间
const poolmomo = require("../poolmomo.js");
const str = "2025-04-26 18:12:30";
const ts = moment.tz(str, "YYYY-MM-DD HH:mm:ss", "Asia/Shanghai").valueOf();
console.log(ts, '----------ts'); // 输出毫秒级时间戳





// (async () => {
//     // 启动浏览器（默认为无头模式）
//     const browser = await puppeteer.launch({
//         args: ['--no-sandbox', '--disable-setuid-sandbox'],
//     });
//     const page = await browser.newPage();

//     try {
//         // 导航到目标网页
//         await page.goto('https://play.google.com/store/apps/details?id=ins.freevideodownload.pro', {
//             waitUntil: 'networkidle0',
//             timeout: 30000 // 增加超时时间
//         });

//         // 等待目标元素加载完成
//         await page.waitForSelector('.VfPpkd-Bz112c-LgbsSe.yHy1rc.eT1oJ.QDwDD.mN1ivc.VxpoF', { timeout: 10000 });

//         // 在浏览器环境中执行 JavaScript 代码，获取元素并点击
//         const result = await page.evaluate(async () => {
//             // 获取所有匹配的选择器元素
//             const elements = document.querySelectorAll('.VfPpkd-Bz112c-LgbsSe.yHy1rc.eT1oJ.QDwDD.mN1ivc.VxpoF');

//             if (elements.length > 0) {
//                 // 点击第一个匹配的元素
//                 elements[0].click();

//                 // 等待元素点击后的变化
//                 await new Promise(resolve => setTimeout(resolve, 2000));

//                 // 弹出框
//                 const asElements = document.querySelectorAll('.VfPpkd-wzTsW');
//                 let h5s = asElements[0].getElementsByTagName('h5');
//                 let title = h5s[0].textContent;
//                 let icons = asElements[0].getElementsByTagName('img');
//                 let icon = icons[0].src;

//                 // 查找短描述
//                 let shortDescription = '';
//                 const sfzrhdElements = document.getElementsByClassName('SfzRHd');
//                 for (let i = 0; i < sfzrhdElements.length; i++) {
//                     const metaTags = sfzrhdElements[i].getElementsByTagName('meta');
//                     for (let j = 0; j < metaTags.length; j++) {
//                         if (metaTags[j].getAttribute('itemprop') === 'description') {
//                             shortDescription = metaTags[j].getAttribute('content');
//                             break;
//                         }
//                     }
//                     if (shortDescription) break;
//                 }

//                 // 查找长描述
//                 let longDescription = '';
//                 const longDescriptionElement = document.querySelector('div[jsname="bN97Pc"]');
//                 if (longDescriptionElement && longDescriptionElement.firstElementChild) {
//                     longDescription = longDescriptionElement.firstElementChild.textContent;
//                 }

//                 if (asElements.length > 0) {
//                     const vElements = asElements[0].querySelectorAll('.reAt0');
//                     let content = {
//                         title: title,
//                         icon: icon,
//                         version: vElements[0].textContent,
//                         shortDescription: shortDescription,
//                         longDescription: longDescription,
//                         version_update_data: vElements[8].textContent
//                     };
//                     return content;
//                 }
//             }
//             return null;
//         });

//         console.log('result:', result); // 打印最终结果

//     } catch (error) {
//         console.error('执行过程中出错:', error);
//     } finally {
//         console.log('关闭浏览器');
//         // 关闭浏览器
//         await browser.close();
//     }
// })();

// 封装一个格式化日期的函数
function formatDate(date) {
    const year = date.getFullYear();
    const month = String(date.getMonth() + 1).padStart(2, "0");
    const day = String(date.getDate()).padStart(2, "0");
    const hours = String(date.getHours()).padStart(2, "0");
    const minutes = String(date.getMinutes()).padStart(2, "0");
    const seconds = String(date.getSeconds()).padStart(2, "0");
    return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
}

// taskOne 拉取评论
const taskOne = () => {
    // 您的任务逻辑
    if (process.env.HOSTNAME.includes("170-187-230-149")) {
        //全能下载器
        getCommentFromGp("download.video.downloader.videodownloader.mp4");
        getCommentFromGp("download.video.downloader.videodownloader.instagram");
        getCommentFromGp("roku.remote.control.tv.remotecontrol");
        getCommentFromGp("roku.remote.control.tv.remoteforroku");
        getCommentFromGp("scan.qr.code.barcode.scanner");
        getCommentFromGp("ins.freevideodownload.pro");

        getCommentFromGp("print.smart.printer.pdf.docs.scanner");
        getCommentFromGp("food.scanner.calorie.counter.cal.ai");
         

        console.log("当前在170.187.230.149");
    } else if (process.env.HOSTNAME.includes("172-104-35-128")) {
        console.log("当前在172.104.35.128");
    } else if (process.env.HOSTNAME.includes("dev01-jp")) {
        console.log("当前在45.76.212.42");
        monitorCompetitorProducts()
    } else if (process.env.HOSTNAME.includes("139-177-190-31")) {
        console.log("当前在139.177.190.31");
        getCommentFromGp("tv.roku.remote.control.tvremote");
        getCommentFromGp("tv.remote.firetv.firestick");
        getCommentFromGp("samsung.remote.control.samsungtv");
        getCommentFromGp("tv.remote.control.lg.tvremote");

    }
};
//回复评论
const taskTwo = () => {

    // 您的任务逻辑
    if (process.env.HOSTNAME.includes("170-187-230-149")) {
        //全能下载器
        reviewReplyTaskMultiJob("download.video.downloader.videodownloader.mp4");
        reviewReplyTaskMultiJob("download.video.downloader.videodownloader.instagram");
        reviewReplyTaskMultiJob("ins.freevideodownload.pro");
        reviewReplyTaskMultiJob("roku.remote.control.tv.remotecontrol");
        reviewReplyTaskMultiJob("roku.remote.control.tv.remoteforroku");

        console.log("当前在170.187.230.149");
    } else if (process.env.HOSTNAME.includes("172-104-35-128")) {
        console.log("当前在172.104.35.128");
    } else if (process.env.HOSTNAME.includes("dev01-jp")) {
        console.log("当前在45.76.212.42");

    } else if (process.env.HOSTNAME.includes("192-46-226-198")) {
        console.log("当前在192.46.226.198");
    } else if (process.env.HOSTNAME.includes("139-177-190-31")) {
        console.log("当前在139.177.190.31");
        reviewReplyTaskMultiJob("tv.roku.remote.control.tvremote");
        reviewReplyTaskMultiJob("tv.remote.firetv.firestick");
        reviewReplyTaskMultiJob("samsung.remote.control.samsungtv");
        reviewReplyTaskMultiJob("tv.remote.control.lg.tvremote");
    }
};
const taskThree = () => {
    // console.log('dddddddddddddd')
    // iZbp1h1gu3djkpokro224cZ
    // console.log(process.env.HOSTNAME)
    // 您的任务逻辑
    if (process.env.HOSTNAME.includes("dev01-jp")) {
    }
};
const taskFour = () => {
    const chinaTime = new Date();
    // console.log("我每天凌晨0点开始执行----" + chinaTime.toLocaleString());
    // 您的任务逻辑
    if (process.env.HOSTNAME.includes("dev01-jp")) {
        console.log("我每天凌晨0点开始执行----" + chinaTime.toLocaleString());
        insertSuccessfulRenewal('com.seamobitech.UniversalTVRemoteControl')

    }
};



const taskFive = () => {
    // 您的任务逻辑
    if (process.env.HOSTNAME.includes("dev01-jp")) {

        //插入每天的试用人数
        // insertDailyTrialCounts('com.seamobitech.UniversalTVRemoteControl')
        //拉取漏发的通知
        getMissHistoryByPk('com.seamobitech.roku')
        getMissHistoryByPk('com.seamobitech.UniversalTVRemoteControl')
        getMissHistoryByPk('com.seamobitech.whatsscanwebclone')
        getMissHistoryByPk('com.seamobitech.QRCodeReadAndGenerate')
        getMissHistoryByPk('com.seamobitech.BeautyAI')
        getMissHistoryByPk('com.seamobitech.HeartPulse')
    }
};

const taskSix = () => {
    // 您的任务逻辑
    if (process.env.HOSTNAME.includes("dev01-jp")) {

        insertSuccessfulRenewal('com.seamobitech.roku')
        // insertDailyTrialCounts('com.seamobitech.roku')
    }
};

const taskSeven = () => {
    // 您的任务逻辑
    if (process.env.HOSTNAME.includes("dev01-jp")) {
        //计算扫脸tokens
        scheduleTokenDistribution()
        //卡路里
        scheduleCalorieTokenDistribution()
        //ainote
        scheduleNoteTokenDistribution()
        // insertDailyTrialCounts('com.seamobitech.whatsscanwebclone')
        // insertDailyTrialCounts('com.seamobitech.QRCodeReadAndGenerate')
        // insertDailyTrialCounts('com.seamobitech.BeautyAI')

        insertSuccessfulRenewal('com.seamobitech.whatsscanwebclone')
        insertSuccessfulRenewal('com.seamobitech.QRCodeReadAndGenerate')
        insertSuccessfulRenewal('com.seamobitech.BeautyAI')
        insertSuccessfulRenewal('com.seamobitech.HeartPulse')
        insertSuccessfulRenewal('com.zhijian.remoteControl')

        //汇总每天每人启动app
        dailyAppOpenSummaryJob()
        //汇总刷新率上报-每天
        aggregateDailySummary()
    }
};
const test = () => {
    if (process.env.HOSTNAME.includes("dev01-jp")) {
        //monitorCompetitorProducts()

    }

};

// 定义定时任务
const startCronJobs = () => {
    // 每2小时执行一次taskOne
    cron.schedule("0 */2 * * *", taskOne);
    // cron.schedule("06 * * * *", taskOne);
    // 每小时执行taskTwo
    cron.schedule("0 */1 * * *", taskTwo);
    //每分钟
    cron.schedule("* * * * *", taskThree);
    // 每天的0点1分执行taskFour 计算周续费率
    cron.schedule("20 0 * * *", taskFour);

    cron.schedule("1 0 * * *", taskFive);
    cron.schedule("1 3 * * *", taskSix);
    cron.schedule("1 5 * * *", taskSeven);
    //每两小时执行一次 wa上报
    cron.schedule("0 */2 * * *", checkAlert);
    cron.schedule('1 * * * *', aggregateHourly);

    //5分钟一次 测试用
    cron.schedule('*/5 * * * *', test);

    // 2025-06-18 0点0分定时降级任务（北京时间）
    cron.schedule('0 0 18 6 *', downgradeTemporaryMembers, {
        timezone: "Asia/Shanghai"
    });

    // 每天凌晨零点10分，仅在指定服务器执行
    cron.schedule("10 0 * * *", aggregateDailyUsage);

    // 每天早上8点检查 temporary_member 是否过期，如过期则降级为 regular，并重置配额
    cron.schedule("0 8 * * *", checkAndDowngradeTemporaryMembers, {
        timezone: "Asia/Shanghai"
    });
};

// 新增函数
async function checkAndDowngradeTemporaryMembers() {
    if (process.env.HOSTNAME.includes("iZbp1h1gu3djkpokro224cZ")) {
        try {
            // 1. 查询所有 temporary_member 用户
            const users = await mysqlQueryMOMO(
                `SELECT * FROM transir_users WHERE user_type = 'temporary_member'`,
                []
            );

            // 2. 当前北京时间戳
            const currentTime = momentx().tz("Asia/Shanghai").valueOf();

            for (const user of users) {
                const subscriptionExpiryDate = user.subscription_expiry_date
                    ? momentx(user.subscription_expiry_date).tz("Asia/Shanghai").valueOf()
                    : null;

                // 3. 检查是否应该降级
                if (!subscriptionExpiryDate || subscriptionExpiryDate < currentTime) {
                    // 查询普通会员配额配置
                    const [config] = await mysqlQueryMOMO(
                        `SELECT 
                        regular_feature_minutes AS feature_minutes, 
                        regular_photos_limit AS photos,
                        regular_tokens_limit AS tokens,
                        regular_float_copy_limit AS float_copy,
                        regular_quick_text_limit AS quick_text
                     FROM transir_system_configs 
                     LIMIT 1`,
                        []
                    );

                    if (!config) continue; // 容错，无配置直接跳过

                    // 解析配额
                    const {
                        feature_minutes,
                        photos,
                        tokens,
                        float_copy,
                        quick_text
                    } = config;

                    const regular_photos_limit = photos || 0;
                    const regular_photos_used = user.regular_photos_used || 0;
                    const photos_left = Math.max(0, regular_photos_limit - regular_photos_used);

                    const lastUsageResetDate = user.subscription_expiry_date
                        ? momentx(user.subscription_expiry_date).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss")
                        : momentx().tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                    const nextResetDate = user.subscription_expiry_date
                        ? momentx(user.subscription_expiry_date).tz("Asia/Shanghai").add(1, 'month').format("YYYY-MM-DD HH:mm:ss")
                        : momentx().tz("Asia/Shanghai").add(1, 'month').format("YYYY-MM-DD HH:mm:ss");

                    // 4. 执行降级和配额刷新
                    await mysqlQueryMOMO(
                        `UPDATE transir_users SET
                        user_type = 'regular',
                        subscription_expiry_date = NULL,
                        feature_minutes         = ?,
                        feature_minutes_used    = 0,
                        feature_minutes_left    = ?,
                        photos                  = ?,
                        photos_used             = ?,
                        photos_left             = ?,
                        tokens                  = ?,
                        tokens_used             = 0,
                        tokens_left             = ?,
                        float_copy              = ?,
                        float_copy_used         = 0,
                        float_copy_left         = ?,
                        quick_text              = ?,
                        quick_text_used         = 0,
                        quick_text_left         = ?,
                        last_usage_reset_date   = ?,
                        next_usage_reset_date   = ?
                     WHERE user_id = ?`,
                        [
                            feature_minutes,
                            feature_minutes,
                            regular_photos_limit,
                            regular_photos_used,
                            photos_left,
                            tokens,
                            tokens,
                            float_copy,
                            float_copy,
                            quick_text,
                            quick_text,
                            lastUsageResetDate,
                            nextResetDate,
                            user.user_id
                        ]
                    );

                    // 日志
                    console.log(`[会员降级] 用户${user.user_id} 已失效，从temporary_member降为regular并刷新配额`);
                }
            }
            console.log(`[会员降级] task finished at`, moment().tz("Asia/Shanghai").format('YYYY-MM-DD HH:mm:ss'));
        } catch (err) {
            console.error(`[会员降级] 定时任务异常:`, err);
        }
    }
}

/**
 * 这是在到期时将临时会员用户全部降级为普通用户的定时任务
 */
async function downgradeTemporaryMembers() {
    if (process.env.HOSTNAME.includes("iZbp1h1gu3djkpokro224cZ")) {
        try {
            // 1. 定义目标到期日 “2025-06-18 00:00:00” 北京时间字符串
            const expiryDate = '2025-06-18 00:00:00';

            // 2. 查询所有需要降级的temporary_member用户
            const querySql = `
                SELECT user_id FROM transir_users 
                WHERE user_type = 'temporary_member'
                AND subscription_expiry_date = ?
            `;
            const users = await mysqlQueryMOMO(querySql, [expiryDate]);
            if (!users || users.length === 0) {
                console.log("没有需要降级的临时会员用户");
                return;
            }
            // 3. 查询普通用户配额配置
            const configSql = 'SELECT regular_feature_minutes, regular_photos_limit FROM transir_system_configs LIMIT 1';
            const configs = await mysqlQueryMOMO(configSql, []);
            let featureMinutes = 0, photos = 0;
            if (configs.length > 0) {
                featureMinutes = configs[0].regular_feature_minutes || 0;
                photos = configs[0].regular_photos_limit || 0;
            }

            // 4. 初始化需要更新的字段
            // 获取当前北京时间
            const nowStr = moment().tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
            // 下次用量重置日，设为一个月后
            const nextUsageResetDate = moment().tz("Asia/Shanghai").add(1, 'month').format("YYYY-MM-DD HH:mm:ss");

            // 5. 依次将所有用户更新为普通用户，并恢复配额与基准
            for (let user of users) {
                const updateSql = `
                    UPDATE transir_users SET
                        user_type = 'regular',
                        feature_minutes = ?,
                        feature_minutes_left = ?,
                        photos = ?,
                        photos_left = ?,
                        usage_base_date = ?,
                        last_usage_reset_date = ?,
                        next_usage_reset_date = ?,
                        subscription_start_date = NULL,
                        subscription_expiry_date = NULL,
                        updated_at = ?
                    WHERE user_id = ?
                `;
                await mysqlQuery(updateSql, [
                    featureMinutes,   // 恢复普通用户配额
                    featureMinutes,   // 剩余额度重置
                    photos,
                    photos,
                    nowStr,           // usage_base_date
                    nowStr,
                    nextUsageResetDate,
                    nowStr,           // updated_at
                    user.user_id
                ]);
            }

            console.log(`成功将${users.length}个临时会员用户降级为普通用户`);

        } catch (err) {
            console.error("降级临时会员用户出错:", err);
        }
    }

}

/**
 * 定时进行每日功能统计（只在主服务器执行）
 */
async function aggregateDailyUsage() {
    // 只在主服务节点执行
    if (!process.env.HOSTNAME.includes("iZbp1h1gu3djkpokro224cZ")) return;
    try {
        console.log('[定时统计] 开始每日功能使用量定时统计...');
        const statDate = momentx().tz("Asia/Shanghai").subtract(1, 'day').format('YYYY-MM-DD');
        // 首先，统计昨日注册与活跃人数并落入统计表
        await doStatUserRegisterAndActive(statDate);

        await doStatForDate(statDate);
        console.log(`[定时统计] 完成: ${statDate}`);
    } catch (err) {
        console.error('每日功能使用统计定时任务出错：', err);
    }
}

/**
 * 统计某一天的注册人数 & 活跃人数，并插入 transir_daily_user_stat 表
 * @param {string} statDate - 格式为 yyyy-MM-dd
 */
async function doStatUserRegisterAndActive(statDate) {
    // 统计起止范围（比如：'2024-06-13 00:00:00' 到 '2024-06-13 23:59:59'）
    const dateBegin = `${statDate} 00:00:00`;
    const dateEnd = `${statDate} 23:59:59`;
    // 1. 统计该日注册人数
    // 用 registration_time 落在该日的数据
    const regCountResult = await mysqlQueryMOMO(
        `
        SELECT COUNT(*) AS cnt
        FROM transir_users
        WHERE registration_time BETWEEN ? AND ?
        `,
        [dateBegin, dateEnd]
    );
    const regCount = regCountResult[0].cnt || 0;

    // 2. 统计该日活跃人数
    // 用 last_login_time 落在该日的数据（注意去重 user_id，防多次登录仅算1人）
    const activeCountResult = await mysqlQueryMOMO(
        `
        SELECT COUNT(DISTINCT user_id) AS cnt
        FROM transir_users
        WHERE last_login_time BETWEEN ? AND ?
        `,
        [dateBegin, dateEnd]
    );
    const activeCount = activeCountResult[0].cnt || 0;

    // 3. 写入 transir_daily_user_stat 表（防止重复插入，建议用 ON DUPLICATE KEY UPDATE）
    await mysqlQueryMOMO(
        `
        INSERT INTO transir_daily_user_stat (stat_date, reg_count, active_count)
        VALUES (?, ?, ?)
        ON DUPLICATE KEY UPDATE
            reg_count = VALUES(reg_count),
            active_count = VALUES(active_count),
            updated_at = NOW()
        `,
        [statDate, regCount, activeCount]
    );

    console.log(`[统计落表] ${statDate} 注册人数：${regCount}，活跃人数：${activeCount}`);
}

/**
 * 实际统计并入库
 * @param {string} statDate 统计日期
 */
async function doStatForDate(statDate) {
    // 1. 统计各功能的总数据
    const statSql = `
        SELECT 
            feature_type, 
            COUNT(DISTINCT user_id) AS user_count,
            SUM(usage_amount) AS total_usage_amount,
            SUM(usage_count) AS total_usage_count
        FROM transir_usage_logs
        WHERE created_date = ?
        GROUP BY feature_type
    `;
    const statResult = await mysqlQueryMOMO(statSql, [statDate]);
    // 2. 拼装并写入统计表
    for (let item of statResult) {
        const {
            feature_type,
            user_count,
            total_usage_amount,
            total_usage_count
        } = item;

        const avg_usage_amount = user_count > 0 ? (total_usage_amount / user_count) : 0;
        const avg_usage_count = user_count > 0 ? (total_usage_count / user_count) : 0;

        // 主键唯一索引控制，已有更新，无则插入
        const updateSql = `
            INSERT INTO transir_daily_usage_stats
            (stat_date, feature_type, user_count, total_usage_amount, total_usage_count, avg_usage_amount, avg_usage_count, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
            ON DUPLICATE KEY UPDATE 
                user_count = VALUES(user_count),
                total_usage_amount = VALUES(total_usage_amount),
                total_usage_count = VALUES(total_usage_count),
                avg_usage_amount = VALUES(avg_usage_amount),
                avg_usage_count = VALUES(avg_usage_count),
                updated_at = NOW()
        `;
        await mysqlQueryMOMO(updateSql, [
            statDate,
            feature_type,
            user_count,
            total_usage_amount,
            total_usage_count,
            avg_usage_amount,
            avg_usage_count
        ]);
    }
}

const waReport = () => {
    // 您的任务逻辑
    if (process.env.HOSTNAME.includes("dev01-jp")) {
        const chinaTime = new Date();
        console.log("每两小时执行一次 wa上报----" + chinaTime.toLocaleString());

    }
};
async function scrapeDataIOS(link) {
    // 启动浏览器（默认为无头模式）
    const browser = await puppeteer.launch({
        args: ['--no-sandbox', '--disable-setuid-sandbox'],
    });
    const page = await browser.newPage();

    try {
        // 导航到目标网页
        await page.goto(link, {
            waitUntil: 'networkidle0',
            timeout: 30000 // 增加超时时间
        });

        // 等待目标元素加载完成
        await page.waitForSelector('.product-header__list__item', { timeout: 10000 });
        // 在浏览器环境中执行 JavaScript 代码，获取相关数据
        const result = await page.evaluate(async () => {
            //appname
            const h1Element = document.getElementsByTagName('h1')[0];
            const childNodes = h1Element.childNodes;
            let titleText = '';
            childNodes.forEach(node => {
                if (node.nodeType === Node.TEXT_NODE) {
                    titleText += node.textContent.trim();
                }
            });
            //icon
            const picture = document.getElementsByTagName('picture')[0];
            const source = picture.getElementsByTagName('source')[0];
            const srcset = source.getAttribute('srcset');
            const srcsetEntries = srcset.split(',');
            const firstEntry = srcsetEntries[0].trim();
            //短描述
            const h2Element = document.querySelector('h2.product-header__subtitle.app-header__subtitle');
            //长描述
            let weClampDivText = null
            const sections = document.getElementsByTagName('section');
            for (const section of sections) {
                const weClampDiv = section.querySelector('div.we-clamp');
                if (weClampDiv) {
                    weClampDivText = weClampDiv.textContent;
                }
            }
            // 获取应用版本号和发布时间
            const section = document.querySelector('section.whats-new');
            const timeElement = section.querySelector('time');
            const date = timeElement.getAttribute('aria-label'); // 或者直接 timeElement.textContent
            const versionElement = section.querySelector('.whats-new__latest__version');
            const version = versionElement.textContent;
            const content = {
                title: titleText,
                icon: firstEntry.split(' ')[0],
                version: version,
                shortDescription: h2Element ? h2Element.textContent : '',
                longDescription: weClampDivText,
                version_update_data: date
            };


            return content;
        });

        // console.log('result:', result); // 打印最终结果
        return result;
    } catch (error) {
        console.error('执行过程中出错:', error);
        throw error;
    } finally {
        console.log('关闭浏览器');
        await browser.close();
    }
}
//竞品监控
async function monitorCompetitorProducts() {

    try {
        // 1. 从 app_store_competitor 表中获取所有 state=0 的竞品数据
        const competitorQuery = `
            SELECT id, link, type ,chat_id
            FROM app_store_competitor 
            WHERE state = 0 
            ORDER BY create_date ASC   
        `;
        const competitors = await mysqlQuery(competitorQuery, []);
        // console.log(competitors, 'competitors')
        // 2. 循环处理每一条竞品数据
        for (const competitor of competitors) {
            const { id, link, type, chat_id } = competitor; // 当前竞品数据的 id, link, type
            // console.log(competitor)
            try {
                // console.log(`开始处理竞品数据 ID: ${id}, 链接: ${link}`);

                // 3. 调用爬虫函数获取数据
                let scrapeResult = null
                if (type == 'android') {
                    scrapeResult = await scrapeData(link);
                } else {
                    scrapeResult = await scrapeDataIOS(link);
                }
                // 如果爬取结果为 null，跳过后续处理
                if (!scrapeResult) {
                    // console.log(`链接 ${link} 未获取到数据，跳过`);
                    continue;
                }

                // 4. 查询 app_store_competitor_tracking 表中对应 link_id 的最新一条 state=0 的记录
                const trackingQuery = `
                    SELECT 
                        app_name, icon, version, 
                        shortDescription, longDescription, version_update_date 
                    FROM app_store_competitor_tracking 
                    WHERE link_id = ? AND state = 0 
                    ORDER BY create_date DESC 
                    LIMIT 1
                `;
                const latestTracking = await mysqlQuery(trackingQuery, [id]);

                // 5. 判断是否需要插入新数据，并找出不一致的字段
                let isSame = true;
                const differences = {};

                if (latestTracking.length > 0) {
                    const oldData = latestTracking[0];

                    if (oldData.app_name !== scrapeResult.title) {
                        isSame = false;
                        differences.app_name = {
                            old: oldData.app_name,
                            new: scrapeResult.title
                        };
                    }
                    if (oldData.icon !== scrapeResult.icon) {
                        isSame = false;
                        differences.icon = {
                            old: oldData.icon,
                            new: scrapeResult.icon
                        };
                    }
                    if (oldData.version !== scrapeResult.version) {
                        isSame = false;
                        differences.version = {
                            old: oldData.version,
                            new: scrapeResult.version
                        };
                    }
                    if (oldData.shortDescription) {
                        if (oldData.shortDescription !== scrapeResult.shortDescription) {
                            isSame = false;
                            differences.shortDescription = {
                                old: oldData.shortDescription,
                                new: scrapeResult.shortDescription
                            };
                        }
                    }

                    if (oldData.longDescription !== scrapeResult.longDescription) {
                        isSame = false;
                        differences.longDescription = {
                            old: oldData.longDescription,
                            new: scrapeResult.longDescription
                        };
                    }
                    // if (oldData.version_update_date !== scrapeResult.version_update_data) {
                    //     isSame = false;
                    //     differences.version_update_date = {
                    //         old: oldData.version_update_date,
                    //         new: scrapeResult.version_update_data
                    //     };
                    // }
                } else {
                    isSame = false; // 无历史记录时也认为数据有变化
                }

                if (!isSame) {
                    // console.log('发现以下不同字段：', differences);
                    const updatedFields = Object.keys(differences);
                    // console.log('更新的字段有：', updatedFields.join(', '));
                    // 触发插入新数据等操作...
                    // console.log(`检测到数据变化，插入新记录，竞品ID: ${id}`);
                    const trackingId = utils.createId(); // 生成唯一ID
                    const currentDate = utils.formatDate(new Date());

                    // 构建插入语句
                    const insertSql = `
                        INSERT INTO app_store_competitor_tracking (
                            id, link_id, type, app_name, icon, version, 
                            shortDescription, longDescription, version_update_date, 
                            create_date, update_date
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    `;
                    const params = [
                        trackingId,
                        id,              // 来自竞品表的 id
                        type,            // 来自竞品表的 type
                        scrapeResult.title,
                        scrapeResult.icon,
                        scrapeResult.version,
                        scrapeResult.shortDescription,
                        scrapeResult.longDescription,
                        scrapeResult.version_update_data,
                        currentDate,     // 创建时间
                        currentDate      // 更新时间
                    ];

                    // 执行插入操作
                    await mysqlQuery(insertSql, params);
                    // console.log(`竞品ID ${id} 数据已更新`);
                    let token = await msgUtils.getTenantAccessToken();
                    let msg_type = "interactive";
                    // let chatid = `oc_300d536417e7a4d6a47c27bc43b00bb0`;
                    let chatid = chat_id;
                    let img = await uploadImage(scrapeResult.icon, token);
                    let at = `<at id="g1eff8g3">(产品)</at><at id="8d333cd2"></at><at id="5bfbfa32">(测试)</at><at id="c47a29d7">(测试)</at>`
                    let contentMsg = {
                        schema: "2.0",
                        config: {
                            update_multi: true,
                            style: {
                                text_size: {
                                    normal_v2: {
                                        default: "normal",
                                        pc: "normal",
                                        mobile: "heading"
                                    }
                                }
                            }
                        },
                        body: {
                            direction: "vertical",
                            padding: "12px 12px 12px 12px",
                            elements: [
                                {
                                    tag: "column_set",
                                    columns: [
                                        {
                                            tag: "column",
                                            width: "weighted",
                                            elements: [
                                                {
                                                    tag: "div",
                                                    text: {
                                                        tag: "plain_text",
                                                        content: "",
                                                        text_size: "normal_v2",
                                                        text_align: "left",
                                                        text_color: "default"
                                                    },
                                                    margin: "0px 0px 0px 0px"
                                                },
                                                {
                                                    tag: "markdown",
                                                    content: `更新内容:${updatedFields.join(', ')}\n\n[竞品链接](${link})\n\n竞品名称:${scrapeResult.title}\n\n当前版本:${scrapeResult.version}\n\n[打开运营后台](https://insdoss.freeapptools.com/#/appMonitor)`,
                                                    text_align: "left",
                                                    text_size: "normal_v2",
                                                    margin: "0px 0px 0px 0px"
                                                }
                                            ],
                                            vertical_align: "top",
                                            weight: 1
                                        },
                                        {
                                            tag: "column",
                                            width: "auto",
                                            elements: [
                                                {
                                                    tag: "img",
                                                    img_key: img,
                                                    preview: true,
                                                    scale_type: "crop_center",
                                                    size: "medium"
                                                }
                                            ],
                                            vertical_align: "top"
                                        }
                                    ]
                                }
                            ]
                        },
                        header: {
                            title: {
                                tag: "plain_text",
                                content: `竞品更新通知`
                            },
                            template: "yellow",
                            padding: "12px 12px 12px 12px"
                        }
                    };
                    let jsonString = JSON.stringify(contentMsg);
                    await msgUtils.sendMessage(chatid, msg_type, jsonString, token);


                } else {
                    console.log('数据一致，无需更新');
                }
            } catch (error) {
                console.error(`处理竞品ID ${id} 时发生错误:`, error);
                // 可选：将竞品表 state 标记为 2（抓取失败）
                // await mysqlQuery(
                //     'UPDATE app_store_competitor SET state = 2 WHERE id = ?',
                //     [id]
                // );
            }
        }

        console.log('所有竞品数据处理完成');
    } catch (error) {
        console.error('竞品监控全局错误:', error);
        throw error;
    }
}
async function uploadImage(url, token) {
    // 将图片 URL 转换为二进制流
    const binaryImage = await urlToBinary(url);
    const response = await axios({
        method: 'post',
        url: 'https://open.feishu.cn/open-apis/im/v1/images',
        headers: {
            'Authorization': `Bearer ${token}`,
            'Content-Type': 'multipart/form-data'
        },
        data: {
            image_type: 'message',
            image: binaryImage
        }
    });
    return response.data.data.image_key;
}
async function urlToBinary(url) {
    const response = await axios.get(url, { responseType: 'arraybuffer' });
    return response.data;
}
/* 代码注释说明：
1. 数据获取：优先按 create_date 顺序处理，确保公平性
2. 逐条处理：使用 for...of 保证顺序执行，避免并发问题
3. 字段对比逻辑：
   - app_name 对应 scrapeResult.title
   - version_update_date 对应 scrapeResult.version_update_data
4. 错误处理：单个竞品出错不影响整体流程，记录日志并标记状态
5. 状态更新：如抓取失败，将竞品 state 设为 2（需确保数据库有此状态定义）
*/
// 封装 Puppeteer 操作的函数
async function scrapeData(link) {
    // 启动浏览器（默认为无头模式）
    const browser = await puppeteer.launch({
        args: ['--no-sandbox', '--disable-setuid-sandbox'],
    });
    const page = await browser.newPage();

    try {
        // 导航到目标网页
        await page.goto(link, {
            waitUntil: 'networkidle0',
            timeout: 30000 // 增加超时时间
        });

        // 等待目标元素加载完成
        await page.waitForSelector('.VfPpkd-Bz112c-LgbsSe.yHy1rc.eT1oJ.QDwDD.mN1ivc.VxpoF', { timeout: 10000 });

        // 在浏览器环境中执行 JavaScript 代码，获取相关数据
        const result = await page.evaluate(async () => {
            // 获取所有匹配的选择器元素
            const elements = document.querySelectorAll('.VfPpkd-Bz112c-LgbsSe.yHy1rc.eT1oJ.QDwDD.mN1ivc.VxpoF');

            if (elements.length > 0) {
                // 点击第一个匹配的元素
                elements[0].click();

                // 等待元素点击后的变化
                await new Promise(resolve => setTimeout(resolve, 2000));

                // 弹出框中获取数据
                const asElements = document.querySelectorAll('.VfPpkd-wzTsW');
                if (asElements.length === 0) {
                    return null;
                }
                const h5s = asElements[0].getElementsByTagName('h5');
                const title = h5s[0].textContent;
                const icons = asElements[0].getElementsByTagName('img');
                const icon = icons[0].src;

                // 查找短描述
                let shortDescription = '';
                const sfzrhdElements = document.getElementsByClassName('SfzRHd');
                for (let i = 0; i < sfzrhdElements.length; i++) {
                    const metaTags = sfzrhdElements[i].getElementsByTagName('meta');
                    for (let j = 0; j < metaTags.length; j++) {
                        if (metaTags[j].getAttribute('itemprop') === 'description') {
                            shortDescription = metaTags[j].getAttribute('content');
                            break;
                        }
                    }
                    if (shortDescription) break;
                }

                // 查找长描述
                let longDescription = '';
                const longDescriptionElement = document.querySelector('div[jsname="bN97Pc"]');
                if (longDescriptionElement && longDescriptionElement.firstElementChild) {
                    longDescription = longDescriptionElement.firstElementChild.textContent;
                }

                // 获取版本信息以及其他数据
                const vElements = asElements[0].querySelectorAll('.reAt0');
                const content = {
                    title: title,
                    icon: icon,
                    version: vElements[0] ? vElements[0].textContent : '',
                    shortDescription: shortDescription,
                    longDescription: longDescription,
                    version_update_data: vElements[8] ? vElements[8].textContent : ''
                };

                return content;
            }
            return null;
        });

        // console.log('result:', result); // 打印最终结果
        return result;
    } catch (error) {
        console.error('执行过程中出错:', error);
        throw error;
    } finally {
        console.log('关闭浏览器');
        await browser.close();
    }
}
async function updateTokenCodes() {
    try {
        const rows = await mysqlQuery('SELECT id, type, message FROM ai_tokens WHERE code IS NULL AND state=0');

        for (const row of rows) {
            try {
                const messageData = JSON.parse(row.message);
                const content = messageData.content;

                // 增强版JSON提取逻辑
                let jsonStr;
                if (content.includes('```json')) {
                    // 处理带代码块的情况
                    jsonStr = content.split('```json')[1].split('```')[0].trim();
                } else {
                    // 处理直接包含JSON的情况
                    jsonStr = content.replace(/^```/gm, '').trim(); // 清除可能的其他代码标记
                }

                // 处理转义字符
                const sanitizedJson = jsonStr
                    .replace(/\\n/g, '')    // 移除JSON中的换行转义
                    .replace(/\\"/g, '"');  // 处理转义引号

                const parsedData = JSON.parse(sanitizedJson);

                // 字段提取逻辑
                let code;
                if (row.type === 'face' || row.type === 'makeup') {
                    code = parsedData.error_info?.error_code ??
                        parsedData.error_code; // 兼容两种格式
                } else if (row.type === 'product') {
                    code = parsedData.warning_info?.warning_code ??
                        parsedData.warning_code;
                }

                if (code === undefined) {
                    console.log(`Skipping id ${row.id}: code not found`);
                    continue;
                }

                await mysqlQuery(
                    'UPDATE ai_tokens SET code = ? WHERE id = ?',
                    [code, row.id]
                );
                console.log(`Updated id ${row.id} with code ${code}`);

            } catch (innerError) {
                console.error(`Error processing record ${row.id}:`, innerError.message);
            }
        }

        console.log('All valid records processed');
    } catch (outerError) {
        console.error('Database operation failed:', outerError.message);
    }
}

// 执行方法
// updateTokenCodes();


function getYesterdayMidnightTimestamp() {
    // 获取当前时间的Date对象
    const now = new Date();

    // 昨天的日期：获取年、月、日，然后设置为昨天
    const yesterday = new Date(now.getFullYear(), now.getMonth(), now.getDate() - 3);

    // 返回昨天0点的毫秒级时间戳
    return yesterday.getTime();
}
//拉取漏发的通知
async function getMissHistoryByPk(pk) {
    const chinaTime = new Date();
    console.log("执行接收服务器历史漏发消息----" + chinaTime.toLocaleString());
    try {
        const jwtToken = createJWT(pk);
        let hasMore = true;
        let paginationToken = null;
        const allNotifications = [];

        while (hasMore) {
            const query = {
                startDate: getYesterdayMidnightTimestamp(),
                endDate: Date.now(),
                onlyFailures: true,
            };

            let requery = `https://api.storekit.itunes.apple.com/inApps/v1/notifications/history`
            // 有条件地添加 paginationToken 到请求体
            if (paginationToken) {
                requery = `https://api.storekit.itunes.apple.com/inApps/v1/notifications/history?paginationToken=${paginationToken}`;
            }
            const response = await axios.post(requery, query, {
                headers: {
                    Authorization: `Bearer ${jwtToken}`,
                    'Content-Type': 'application/json',
                }
            });

            const { notificationHistory, paginationToken: newPaginationToken, hasMore: newHasMore } = response.data;
            allNotifications.push(...notificationHistory);

            paginationToken = newPaginationToken;
            hasMore = newHasMore;
        }
        if (allNotifications.length > 0) {
            allNotifications.forEach(item => {
                //处理 signedPayload
                handleMissedNotifications(item.signedPayload)
            })
        }

    } catch (error) {
        console.error('Error fetching notification history:', error);
    }
}

async function handleMissedNotifications(signedPayload) {
    const decodedToken = jwt.decode(signedPayload, { complete: true });
    if (!decodedToken) {
        console.log('Invalid token')
        return
    }
    const decodedHeader = decodedToken.header;
    // const response = await axios.get('https://appleid.apple.com/auth/keys');
    if (!decodedHeader || !decodedHeader.x5c) {
        // console.error('JWT Header:', decodedHeader);
        console.log('Invalid token: missing x5c')
        return
    }
    // 从 x5c 提取公钥
    const publicKey = getKeyFromX5C(decodedHeader.x5c);
    getKeyFromX5CKid(decodedHeader.x5c)
    // 验证 JWT
    jwt.verify(signedPayload, publicKey, { algorithms: ['ES256'] }, (err, decoded) => {
        if (err) {
            console.error('JWT verification error:', err);
            return
        }

        // 如果解码成功，处理通知
        handleMissedNotification(decoded, publicKey);
    });
}

// 示例：处理漏发的通知

async function handleMissedNotification(notification, publicKey) {
    const rows = await mysqlQuery(
        `SELECT *
FROM js_app_apple_notifications
WHERE notification_UUID = ?`,
        [notification.notificationUUID]
    );
    if (rows.length > 0) {

    } else {
        console.log('自动任务，没有重复的', notification.notificationUUID)

        // 没有重复条目，继续后续操作


        const notificationType = notification.notificationType;

        const jsonPayload = notification.data;

        let notificationBox = {}
        notificationBox.id = utils.createId();
        notificationBox.notification_type = notification.notificationType;
        notificationBox.subtype = notification.subtype;
        notificationBox.notification_UUID = notification.notificationUUID;
        notificationBox.signed_date = momentx(notification.signedDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
        notificationBox.pk = jsonPayload.bundleId;
        notificationBox.consumption_request_reason = jsonPayload.consumptionRequestReason;
        notificationBox.status = jsonPayload.status;
        notificationBox.environment = jsonPayload.environment;
        notificationBox.create_date = momentx(notification.signedDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
        notificationBox.update_date = momentx(notification.signedDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");

        // 解码和验证signedTransactionInfo
        if (jsonPayload.signedTransactionInfo) {
            jwt.verify(jsonPayload.signedTransactionInfo, publicKey, { algorithms: ['ES256'] }, (err, decodedTransaction) => {
                if (err) {
                    console.error('Transaction JWT verification error:', err);
                } else {
                    // console.log('Decoded Transaction Info:', decodedTransaction);
                    // 处理交易信息
                    notificationBox.transaction_id = decodedTransaction.transactionId;
                    notificationBox.app_account_token = decodedTransaction.appAccountToken;
                    notificationBox.currency = decodedTransaction.currency;
                    notificationBox.expires_date = momentx(decodedTransaction.expiresDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                    notificationBox.in_app_ownership_type = decodedTransaction.inAppOwnershipType;
                    notificationBox.is_upgraded = decodedTransaction.isUpgraded;
                    notificationBox.offer_discount_type = decodedTransaction.offerDiscountType;
                    notificationBox.offer_type = decodedTransaction.offerType;
                    notificationBox.original_purchase_date = momentx(decodedTransaction.originalPurchaseDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                    notificationBox.original_transaction_id = decodedTransaction.originalTransactionId;
                    notificationBox.price = utils.convertToDollars(decodedTransaction.price);
                    notificationBox.product_id = decodedTransaction.productId;
                    notificationBox.purchase_date = momentx(decodedTransaction.purchaseDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                    notificationBox.quantity = decodedTransaction.quantity;
                    notificationBox.revocation_date = momentx(decodedTransaction.revocationDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                    notificationBox.revocation_reason = decodedTransaction.revocationReason;
                    notificationBox.storefront = decodedTransaction.storefront;
                    notificationBox.storefront_id = decodedTransaction.storefrontId;
                    notificationBox.subscription_groupIdentifier = decodedTransaction.subscriptionGroupIdentifier;
                    notificationBox.transaction_reason = decodedTransaction.transactionReason;
                    notificationBox.type = decodedTransaction.type;
                    notificationBox.web_order_lineItem_id = decodedTransaction.webOrderLineItemId;
                }
            });
        }

        // 解码和验证signedRenewalInfo
        if (jsonPayload.signedRenewalInfo) {
            jwt.verify(jsonPayload.signedRenewalInfo, publicKey, { algorithms: ['ES256'] }, (err, decodedRenewal) => {
                if (err) {
                    console.error('Renewal JWT verification error:', err);
                } else {
                    // console.log('Decoded Renewal Info:', decodedRenewal);
                    // 处理续订信息
                    notificationBox.auto_renew_product_id = decodedRenewal.autoRenewProductId;
                    notificationBox.auto_renew_status = decodedRenewal.autoRenewStatus;
                    notificationBox.expiration_intent = decodedRenewal.expirationIntent;
                    notificationBox.grace_period_expires_date = momentx(decodedRenewal.gracePeriodExpiresDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                    notificationBox.is_in_billing_retry_period = decodedRenewal.isInBillingRetryPeriod;
                    notificationBox.recent_subscription_start_date = momentx(decodedRenewal.recentSubscriptionStartDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                    notificationBox.renewal_date = momentx(decodedRenewal.renewalDate).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
                    notificationBox.renewal_price = utils.convertToDollars(decodedRenewal.renewalPrice);
                }
            });
        }

        if (notificationBox.environment == 'Sandbox') {
            notificationBox.state = 1
        }
        const { text, values } = utils.createInsertSql(
            "js_app_apple_notifications",
            notificationBox
        );


        let result = await utils.query(text, values);

        // console.log(result)
        if (notificationType == 'CONSUMPTION_REQUEST') {
            const transactionId = notificationBox.transaction_id;
            await sendConsumptionInfo(notificationBox) //回应苹果
            // 查询数据库，检查是否存在重复记录
            const checkQuery = `
            SELECT is_agree 
            FROM apple_refund_notifications
            WHERE transaction_id = ?
        `;
            let existingRecords = await mysqlQuery(checkQuery, [transactionId]);

            if (existingRecords.length === 0) {
                // 插入新记录
                const { text, values } = utils.createInsertSql("apple_refund_notifications", notificationBox);
                let result2 = await utils.query(text, values);
            } else if (existingRecords.length > 0) {
                // 更新现有记录的 update_date，假设 is_agree 不等于 1
                const updateQuery = `
                UPDATE apple_refund_notifications 
                  SET update_date = ?, create_date = ?, is_agree = 0
                WHERE transaction_id = ?
            `;
                await mysqlQuery(updateQuery, [utils.formatDate(new Date()), utils.formatDate(new Date()), transactionId]);
                console.log('Updated existing record with new update_date');
            }
        }

        if ((notificationType === 'REFUND' || notificationType === 'REFUND_DECLINED')) {
            // 查询数据库，检查是否存在 transaction_id 的记录
            const checkQuery = `
            SELECT id 
            FROM apple_refund_notifications 
            WHERE transaction_id = ?
        `;
            let existingRecords = await mysqlQuery(checkQuery, [notificationBox.transaction_id]);

            if (existingRecords.length > 0) {
                let isAgreeValue;

                if (notificationType === 'REFUND') {
                    isAgreeValue = 2;
                } else if (notificationType === 'REFUND_DECLINED') {
                    isAgreeValue = 1;
                }
                // 获取第一条记录的 ID
                const recordId = existingRecords[0].id;
                // 更新记录的 is_agree 字段
                const updateQuery = `
                UPDATE apple_refund_notifications 
                SET is_agree = ?, update_date = ?
                WHERE id = ?
            `;
                await mysqlQuery(updateQuery, [isAgreeValue, utils.formatDate(new Date()), recordId]);
                console.log(`Updated transaction_id ${notificationBox.transaction_id} with is_agree = ${isAgreeValue}`);
            } else {
                console.log(`Transaction_id ${notificationBox.transaction_id} not found in table.`);
            }
        }


    }


}
async function sendConsumptionInfo(content) {
    const chinaTime = new Date();
    const jwtToken = createJWT(content.pk);
    try {
        const consumptionUrl = `https://api.storekit.itunes.apple.com/inApps/v1/transactions/consumption/${content.transaction_id}`;



        const ConsumptionRequest = {
            accountTenure: 0, //用户的账户年龄
            appAccountToken: '',//app生成的uuid
            consumptionStatus: 3,// 消费状况  1应用内购买不会被消耗 2应用内购买已部分消耗 3应用内购买已全部消耗
            customerConsented: true, //客户是否同意向 App Store 提供消费数据
            deliveryStatus: 0,// 0 app已提供商品，并运行正常 
            lifetimeDollarsPurchased: 0, // 0 未声明终身购买金额。
            lifetimeDollarsRefunded: 0, // 0 终身退款金额未申报。
            platform: 1, // 1苹果平台。
            playTime: 0, //用户使用app的时长  0未声明使用时间。
            refundPreference: 2, //希望拒绝退款
            sampleContentProvided: true, //是否提供试用
            userStatus: 1 //您的应用内客户帐户的状态。
        };
        const config = {
            headers: {
                Authorization: `Bearer ${jwtToken}`,
                'Content-Type': 'application/json',
            },
        };

        const response = await axios.put(consumptionUrl, ConsumptionRequest, config);
        console.log('退款回应成功:-----------------' + response.status + '----------' + content.transaction_id + '-----------' + content.pk + '------' + chinaTime.toLocaleString());
    } catch (error) {
        console.log('退款回应失败:-----------------' + content.transaction_id + '-----------' + content.pk + '------' + chinaTime.toLocaleString());

        console.error('退款回应:', error.response?.data || error.message);
    }
}
// console.log(moment(1743900579000).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss"))
// 使用原生JavaScript

// 从 x5c 中提取公钥
function getKeyFromX5C(x5c) {
    // 将第一个证书从 x5c 中取出并转换为 PEM 格式
    const pemCert = `-----BEGIN CERTIFICATE-----\n${x5c[0]}\n-----END CERTIFICATE-----`;
    // 从 PEM 证书生成公钥
    const publicKey = crypto.createPublicKey(pemCert).export({ type: 'spki', format: 'pem' });
    return publicKey;
}
// 从 x5c 中提取Kid
function getKeyFromX5CKid(x5c) {
    // 将第一个证书从 x5c 中取出并转换为 PEM 格式
    const pemCert1 = `-----BEGIN CERTIFICATE-----\n${x5c[0]}\n-----END CERTIFICATE-----`;

    // 计算证书的 SHA-256 指纹
    const certHash1 = crypto.createHash('sha256');
    certHash1.update(pemCert1);
    const certFingerprint1 = certHash1.digest('hex');
    const pemCert2 = `-----BEGIN CERTIFICATE-----\n${x5c[1]}\n-----END CERTIFICATE-----`;

    // 计算证书的 SHA-256 指纹
    const certHash2 = crypto.createHash('sha256');
    certHash2.update(pemCert2);
    const certFingerprint2 = certHash2.digest('hex');
    const pemCert3 = `-----BEGIN CERTIFICATE-----\n${x5c[2]}\n-----END CERTIFICATE-----`;

    // 计算证书的 SHA-256 指纹
    const certHash3 = crypto.createHash('sha256');
    certHash3.update(pemCert3);
    const certFingerprint3 = certHash3.digest('hex');

    // 将指纹作为 kid 返回
    // return certFingerprint;
}
// 创建 JWT
function createJWT(pk) {
    //原始订单id是串联整个用户所有订单
    //transaction_id是交易id 可以用做用户退款  查看用户这笔订单申请退款 也用来可以看后续这个订单是同意还是拒绝
    let privateKey = null
    let keyId = null
    let teamId = null
    if (pk == 'com.seamobitech.roku') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/iosRoku.p8');
        keyId = 'KPTK8YW54N';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.UniversalTVRemoteControl') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/universal.p8');
        keyId = 'NDVH93RZ93';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.whatsscanwebclone') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/waai.p8');
        keyId = 'M9HSW38PQ7';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.QRCodeReadAndGenerate') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/qr.p8');
        keyId = 'BPK84L5D9P';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.BeautyAI') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/beauty.p8');
        keyId = 'YKSHCJXDZ3';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.HeartPulse') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/iosHeartPulse.p8');
        keyId = '33R7CA35M3';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.zhijian.remoteControl') {
        // SubscriptionKey_UH45FLL4DM
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/universalRemote2.p8');
        keyId = 'UH45FLL4DM';
        teamId = '701e51de-a934-4e08-96e1-5513f30f0d04';
    }
    if (pk == 'com.trans.translationAiAndSubtitle') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/iosTransir.p8');
        keyId = '47KPS4C6NW';
        teamId = '701e51de-a934-4e08-96e1-5513f30f0d04';
    }
    // SubscriptionKey_W587HNCP52  翻译
    const now = Math.floor(Date.now() / 1000);
    const expiresIn = 60 * 60;

    const payload = {
        iss: teamId,
        iat: now,
        exp: now + expiresIn,
        aud: 'appstoreconnect-v1',
        bid: pk
    };
    const token = jwt.sign(payload, privateKey, {
        algorithm: 'ES256',
        keyid: keyId
    });

    return token;
}

//自动回复5星评论
async function reviewReplyTaskMultiJob(pk) {
    // console.log(`---------执行五星评论自动回复---------,包名${pk},当前所在服务器-----------${process.env.HOSTNAME}`)

    let templateList = [];
    //根据包名 先把所有的这个包名下的5星评论取过来
    templateList = await utils.getFiveStarTemplatesByPackageName(pk);
    //if数组不为空,就循环这个数组
    if (templateList.length != 0) {
        // console.log('templateList.length != 0 有模板')
        templateList = shuffleArray(templateList);
        templateList.forEach(async (item) => {
            //每循环一次 就用方法  取符合条件的一条评论数据   包名/语言/is_reply=0/star_rating=5/只取一个/排序随机
            let appComment = await utils.getCommentByPackageAndLanguage(item.package_name, item.reviewer_language)
            if (appComment.length != 0) {
                // console.log('有符合包名/语言/is_reply=0/star_rating=5---去回复')

                let pk = appComment[0].package_name
                let content = item.template_content
                let review_id = appComment[0].review_id
                let tid = item.id
                utils.reviewsReply(pk, content, review_id, tid)
            }
        });
    }

    //然后走回复
}
function shuffleArray(array) {
    for (let i = array.length - 1; i > 0; i--) {
        // 随机选取一个不大于 i 的数
        let j = Math.floor(Math.random() * (i + 1));
        // 交换 i 和 j 的位置
        [array[i], array[j]] = [array[j], array[i]];
    }
    return array;
}
async function getCommentFromGp(packageName) {
    // console.log(`---------执行评论拉取---------,包名${packageName},当前所在服务器-----------${process.env.HOSTNAME}`)
    try {
        // 假设androidPublisherConn是你已经实现了的函数
        const androidPublisher = await androidPublisherConn(packageName);
        let response = await androidPublisher.reviews.list({ packageName });
        let allReviewsList = response.data.reviews || [];
        while (true) {
            // 可以直接使用 while (true) 并在循环内部进行跳出
            await reviewJobTaskReview(allReviewsList, packageName);

            // 检查是否存在nextPageToken来获取更多的评论
            if (
                !response.data.tokenPagination ||
                !response.data.tokenPagination.nextPageToken
            ) {
                break; // 如果没有更多页了，就跳出循环
            }

            let nextPageToken = response.data.tokenPagination.nextPageToken;
            // 发送下一页评论请求
            response = await androidPublisher.reviews.list({
                packageName,
                token: nextPageToken,
            });

            // 检查新获取的评论是否存在，并且累积所有评论
            const newReviews = response.data.reviews;
            if (newReviews && newReviews.length > 0) {
                allReviewsList = allReviewsList.concat(newReviews);
            } else {
                break; // 如果新请求没有返回评论，则跳出循环
            }
        }
    } catch (e) {
        console.error(e);
    }
}
function createInsertSql(tableName, dataObject) {
    const keys = Object.keys(dataObject);
    const values = Object.values(dataObject);

    const columnNames = keys.join(", ");
    const valuePlaceholders = keys.map((key, index) => `?`).join(", ");

    // 对于MySQL，我们不使用 RETURNING * 子句
    const text = `INSERT INTO ${tableName}(${columnNames}) VALUES(${valuePlaceholders})`;

    return {
        text,
        values,
    };
}
async function reviewJobTaskReview(reviewsList, packageName) {
    if (reviewsList.length < 1) {
        // console.log("reviewsList 0");
    } else {
        // 处理评论数据
        for (let review of reviewsList) {
            try {
                let appComment = dealReview(review);
                let commentList = await AppCommentDao.selectByReviewId(review.reviewId);
                if (commentList.length == 0) {
                    appComment.package_name = packageName; // 设置应用包名
                    appComment.id = uuidv4().replace(/-/g, "");
                    // 使用帮助函数生成SQL
                    const { text, values } = createInsertSql(
                        "js_app_comment",
                        appComment
                    );

                    try {
                        let res1 = await utils.query(text, values);
                        // console.log(res1);
                    } catch (insertError) {
                        console.error(
                            "Error inserting appComment into database:",
                            insertError.stack
                        );
                    }
                }
                // if (commentList.length < 1) {
                //   appComment.packageName = packageName; // 设置应用包名
                //   await AppComment.create(appComment); // 存储到数据库
                //   console.log("appComment save:", appComment);
                // }
            } catch (e) {
                console.error(`appComment insert error: ${e} ---- response:`, review);
            }
        }
    }
}

function dealReview(review) {
    const appComment = {};

    if (review) {
        appComment.review_id = review.reviewId;
        appComment.author_name = review.authorName;
        const comments = review.comments;

        if (comments && comments.length > 0) {
            const userComment = comments[0].userComment; // 获取第一个用户评论
            if (userComment) {
                appComment.android_os_version = userComment.androidOsVersion || null;
                appComment.app_version_code = userComment.appVersionCode || null;
                appComment.app_version_name = userComment.appVersionName || null;
                appComment.device = userComment.device || null;
                appComment.device_metadata =
                    (userComment.deviceMetadata &&
                        JSON.stringify(userComment.deviceMetadata)) ||
                    null;
                appComment.last_modified =
                    (userComment.lastModified &&
                        getLsm(JSON.stringify(userComment.lastModified))) ||
                    null; // Assume getLsm is a custom function you will define
                appComment.reviewer_language = userComment.reviewerLanguage || null;
                appComment.star_rating = userComment.starRating || null;
                appComment.content = userComment.text || null;
                appComment.update_by = "admin";
                appComment.create_by = "admin";
            }
        }
    }

    return appComment;
}

function getLsm(input) {
    if (input.includes("seconds")) {
        let seconds;
        let nanos = 0; // 可以忽略，因为Date对象在js中只精确到毫秒

        try {
            // 解析JSON格式的输入
            const jsonObject = JSON.parse(input);
            seconds = jsonObject.seconds;

            if (input.includes("nanos")) {
                nanos = jsonObject.nanos;
            }
        } catch (e) {
            console.error(e);
            return null;
        }

        // 将秒转换为毫秒（丢弃纳秒部分）
        // JavaScript中的Date对象不支持更高的精度
        const totalMillis = seconds * 1000 + nanos / 1e6;

        // 使用moment格式化日期时间
        // moment从UNIX时间戳创建一个日期对象默认会按照UTC时间，
        // 如果要使用特定时区需要moment-timezone包
        const formattedDateTime = moment(totalMillis).format("YYYY-MM-DD HH:mm:ss");
        return formattedDateTime;
    }

    // 返回null如果输入不包括"seconds"字段
    return null;
}

//插入每天试用人数
async function insertDailyTrialCounts(pk) {
    let sql = `SELECT * FROM js_app_apple_notifications WHERE pk = ? AND subtype = 'INITIAL_BUY' AND offer_discount_type = 'FREE_TRIAL' AND environment = 'Production'`;
    let params = [pk];
    try {
        const results = await mysqlQuery(sql, params);
        const dailyCounts = {};
        results.forEach(row => {
            const date = moment(row.update_date).format('YYYY-MM-DD');

            if (!dailyCounts[date]) {
                dailyCounts[date] = 0;
            }
            dailyCounts[date]++;
        });

        const formattedData = Object.keys(dailyCounts).map(date => ({
            date,
            count: dailyCounts[date]
        }));

        // 遍历 formattedData，插入或更新到 new_weekly_renewal_rates 表中
        for (const { date, count } of formattedData) {
            let checkSql = `SELECT * FROM new_weekly_renewal_rates WHERE pk = ? AND base_date = ?`;
            let checkParams = [pk, date];
            const checkResult = await mysqlQuery(checkSql, checkParams);

            if (checkResult.length > 0) {
                // 如果找到记录，更新 daily_trial_count
                let updateSql = `UPDATE new_weekly_renewal_rates SET daily_trial_count = ? WHERE pk = ? AND base_date = ?`;
                let updateParams = [count, pk, date];
                await mysqlQuery(updateSql, updateParams);
            } else {

            }
        }


    } catch (error) {
        console.error(error);

    }
}
// insertNewUserRenewals('com.seamobitech.whatsscanwebclone')
//处理通知分表 原始id整合到一起 按天分组 (每天-第一次续订的人数)
async function insertNewUserRenewals(pk) {
    const chinaTime = new Date();
    console.log('定时任务开始执行---处理通知分表 原始id整合到一起 按天分组 (每天-第一次续订的人数)--执行时间--' + chinaTime.toLocaleString())
    const startTime = new Date();
    // console.log('Start Time:', startTime.toLocaleString());

    const sqlSelect = `
        SELECT 
            original_purchase_date, update_date, original_transaction_id, transaction_id 
        FROM 
            js_app_apple_notifications
        WHERE 
            notification_type = 'DID_RENEW' AND
            pk = ? AND environment <> 'Sandbox'
    `;

    const paramsSelect = [pk];

    try {
        const results = await mysqlQuery(sqlSelect, paramsSelect);

        // const filteredResults = results.filter(row => {
        //     const originalDate = moment(row.original_purchase_date).tz('Asia/Shanghai').format('YYYY-MM-DD');
        //     const updateDate = moment(row.update_date).tz('Asia/Shanghai').subtract(3, 'days').format('YYYY-MM-DD');
        //     if (originalDate === updateDate) {
        //         // console.log(originalDate,updateDate)
        //     }
        //     return originalDate === updateDate;
        // });
        const filteredResults = results.filter(row => {
            const originalDate = moment(row.original_purchase_date).tz('Asia/Shanghai');
            const updateDate = moment(row.update_date).tz('Asia/Shanghai');

            // 检查更新日期是否在原始购买日期之后的三天内
            const withinThreeDays = updateDate.isSameOrAfter(originalDate) && updateDate.diff(originalDate, 'days') <= 3;

            if (withinThreeDays) {
                // console.log(originalDate.format('YYYY-MM-DD'), updateDate.format('YYYY-MM-DD'));
            }

            return withinThreeDays;
        });


        const groupedByDate = filteredResults.reduce((acc, row) => {
            const date = moment(row.update_date).tz('Asia/Shanghai').format('YYYY-MM-DD');
            if (!acc[date]) {
                acc[date] = [];
            }
            acc[date].push(row.original_transaction_id);
            return acc;
        }, {});
        let today1 = moment().tz('Asia/Shanghai').format('YYYY-MM-DD');

        // 获取最早的日期
        let earliestDate1 = moment(Object.keys(groupedByDate).sort()[0]).format('YYYY-MM-DD');
        // 使用 getDatesBetween 获取完整日期列表
        const completeDates = getDatesBetween(earliestDate1, today1);
        // 补齐所有日期
        completeDates.forEach(date => {
            if (!groupedByDate[date]) {
                groupedByDate[date] = [];
            }
        });
        // 在这里使用 async/await 执行附加的数据库查询以获取额外数据
        for (const date of Object.keys(groupedByDate)) {
            const sqlAdditionalSelect = `
        SELECT original_transaction_id 
        FROM js_app_apple_notifications 
        WHERE 
            pk = ? AND 
            DATE(update_date) = ? AND 
            subtype = 'INITIAL_BUY' AND (offer_discount_type IS NULL OR offer_discount_type != 'FREE_TRIAL') AND
            environment <> 'Sandbox'
    `;
            const additionalParamsSelect = [pk, date];

            const additionalResults = await mysqlQuery(sqlAdditionalSelect, additionalParamsSelect);

            // 将查询到的 original_transaction_id 添加到现有的 transaction ID 列表中
            additionalResults.forEach(row => {
                if (!groupedByDate[date].includes(row.original_transaction_id)) {
                    groupedByDate[date].push(row.original_transaction_id);
                }
            });
        }


        const today = moment().tz('Asia/Shanghai').format('YYYY-MM-DD');

        // 获取最早的日期
        const earliestDate = moment(Object.keys(groupedByDate).sort()[0]).format('YYYY-MM-DD');
        // console.log(today)
        // console.log(earliestDate)
        // 获取所有日期
        const allDates = getDatesBetween(earliestDate, today);
        // console.log(allDates,'allDates')


        // 补齐日期
        allDates.forEach(date => {
            if (!groupedByDate[date]) {
                groupedByDate[date] = [];
            }
        });
        // 按日期排序
        const sortedGroupedByDate = Object.keys(groupedByDate)
            .sort()
            .reduce((acc, date) => {
                acc[date] = groupedByDate[date];
                return acc;
            }, {});
        for (const [date, originalTransactionIds] of Object.entries(sortedGroupedByDate)) {
            if (date === today) {
                // console.log(`Skipping today's data for date: ${date}`);
                continue;
            }

            // console.log(`Date: ${date}, OriginalTransactionIds Length: ${originalTransactionIds.length}`);

            const sqlCheck = `
    SELECT count FROM new_successful_renewals WHERE date = ? AND pk = ?
`;
            const paramsCheck = [date, pk];
            const checkResult = await mysqlQuery(sqlCheck, paramsCheck);

            if (checkResult.length === 0 || (checkResult.length > 0 && parseInt(checkResult[0].count, 10) === 0)) {
                const sqlInsert = `
                    INSERT INTO new_successful_renewals (
                        id,
                        date, 
                        originalOrderIds, 
                        pk,
                        count,
                        create_date,
                        update_date
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)
                `;
                const paramsInsert = [
                    utils.createId(),
                    date,
                    originalTransactionIds.join(','),
                    pk,
                    originalTransactionIds.length,
                    utils.formatDate(new Date()),
                    utils.formatDate(new Date())
                ];

                await mysqlQuery(sqlInsert, paramsInsert);
                // console.log(`Inserted data for date: ${date}`);
            }
        }

        // console.log('Data inserted successfully');
    } catch (error) {
        console.error('Error inserting data:', error);
    } finally {
        // const endTime = new Date();
        // console.log('End Time:', endTime.toLocaleString());
        calculateAndStoreWeeklyRenewalRatesNew(pk)
    }
}
// 获取两个日期之间的所有日期
function getDatesBetween(startDate, endDate) {
    const dates = [];
    let currentDate = moment(startDate);
    const end = moment(endDate);
    while (currentDate <= end) {
        dates.push(currentDate.format('YYYY-MM-DD'));
        currentDate.add(1, 'days');
    }
    return dates;
}

// 格式化日期为 YYYY-MM-DD
function formatDate(date) {
    const year = date.getFullYear();
    const month = String(date.getMonth() + 1).padStart(2, '0');
    const day = String(date.getDate()).padStart(2, '0');
    return `${year}-${month}-${day}`;
}
//处理通知分表 原始id整合到一起 按天分组 (每天-总的续订人数)
async function insertSuccessfulRenewal(pk) {
    const chinaTime = new Date();
    // console.log('定时任务开始执行---处理通知分表 原始id整合到一起 按天分组 (每天-总的续订人数)--执行时间--' + chinaTime.toLocaleString())

    const startTime = new Date();
    // console.log('Start Time:', startTime.toLocaleString());


    const sqlSelect = `
        SELECT 
            update_date, original_transaction_id, transaction_id
        FROM 
            js_app_apple_notifications
        WHERE 
            notification_type = 'DID_RENEW' AND environment = 'Production' AND
            pk = ?
    `;

    const paramsSelect = [pk];

    try {
        const results = await mysqlQuery(sqlSelect, paramsSelect);

        const groupedByDate = results.reduce((acc, row) => {
            const dateObj = new Date(row.update_date);
            const date = dateObj.toLocaleDateString('zh-CN', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\//g, '-');
            if (!acc[date]) {
                acc[date] = [];
            }
            acc[date].push(row.original_transaction_id);
            return acc;
        }, {});

        const today = new Date().toLocaleDateString('zh-CN', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\//g, '-');

        for (const [date, originalTransactionIds] of Object.entries(groupedByDate)) {
            if (date === today) {
                // console.log(`Skipping today's data for date: ${date}`);
                continue;
            }

            // console.log(`Date: ${date}, OriginalTransactionIds Length: ${originalTransactionIds.length}`);

            const sqlCheck = `
                SELECT 1 FROM successful_renewals WHERE date = ? AND pk = ?
            `;
            const paramsCheck = [date, pk];
            const checkResult = await mysqlQuery(sqlCheck, paramsCheck);
            if (checkResult.length === 0) {
                const sqlInsert = `
                    INSERT INTO successful_renewals (
                        id,
                        date, 
                        originalOrderIds, 
                        pk,
                        count,
                        create_date,
                        update_date
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)
                `;
                const paramsInsert = [
                    utils.createId(),
                    date,
                    originalTransactionIds.join(','),
                    pk,
                    originalTransactionIds.length,
                    utils.formatDate(new Date()),
                    utils.formatDate(new Date())
                ];

                await mysqlQuery(sqlInsert, paramsInsert);
                // console.log(`Inserted data for date: ${date}`);
            }
        }

        // console.log('Data inserted successfully');
    } catch (error) {
        console.error('Error inserting data:', error);
    } finally {
        const endTime = new Date();
        // console.log('End Time:', endTime.toLocaleString());
        calculateAndStoreWeeklyRenewalRates(pk)
    }
}

//计算周续费率 (每天-总的续订人数)
async function calculateAndStoreWeeklyRenewalRates(pk) {
    const chinaTime = new Date();
    // console.log('定时任务开始执行---计算周续费率 (每天-总的续订人数)--执行时间--' + chinaTime.toLocaleString())
    const startTime = new Date();
    // console.log('开始时间:', startTime.toLocaleString());

    try {
        // console.log(`处理 PK: ${pk}`);

        // 获取所有日期
        const sqlSelectAllDates = `
            SELECT DISTINCT date
            FROM successful_renewals
            WHERE pk = ?
            ORDER BY date
        `;
        const paramsAllDates = [pk];
        const allDatesResults = await mysqlQuery(sqlSelectAllDates, paramsAllDates);
        const allDates = allDatesResults.map(row => row.date);
        // console.log(`找到 ${allDates.length} 个基准日期。`);

        for (const baseDate of allDates) {
            // console.log(`处理基准日期: ${baseDate}`);

            const sqlSelectBaseWeek = `
                SELECT originalOrderIds
                FROM successful_renewals
                WHERE date = ? AND pk = ?
            `;
            const paramsBaseWeek = [baseDate, pk];
            const baseWeekResults = await mysqlQuery(sqlSelectBaseWeek, paramsBaseWeek);

            // 解析 originalOrderIds
            const baseWeekIds = new Set();
            baseWeekResults.forEach(row => {
                const ids = row.originalOrderIds.split(',');
                ids.forEach(id => baseWeekIds.add(id.trim()));
            });

            const initialCount = baseWeekIds.size;
            // console.log(`基准日期 ${baseDate} 的初始人数: ${initialCount}`);/

            let currentDate = new Date(baseDate);
            const weeklyRates = [];

            while (true) {
                // 移动到下一周的同一天
                currentDate.setDate(currentDate.getDate() + 7);
                const nextWeekDate = currentDate.toLocaleDateString('zh-CN', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\//g, '-');

                const sqlSelectNextWeek = `
                    SELECT originalOrderIds
                    FROM successful_renewals
                    WHERE date = ? AND pk = ?
                `;
                const paramsNextWeek = [nextWeekDate, pk];
                const nextWeekResults = await mysqlQuery(sqlSelectNextWeek, paramsNextWeek);

                if (nextWeekResults.length === 0) {
                    // console.log(`没有数据用于周开始于 ${nextWeekDate}。结束循环。`);
                    break; // 没有更多数据
                }

                const nextWeekIds = new Set();
                nextWeekResults.forEach(row => {
                    const ids = row.originalOrderIds.split(',');
                    ids.forEach(id => nextWeekIds.add(id.trim()));
                });

                const continuedRenewals = [...baseWeekIds].filter(id => nextWeekIds.has(id));

                const renewalRate = initialCount ? continuedRenewals.length / initialCount : 0;
                const renewalCount = continuedRenewals.length;
                weeklyRates.push({ week: nextWeekDate, rate: renewalRate, count: renewalCount });

                // console.log(`基准日期: ${baseDate}, 周: ${nextWeekDate}, 续费率: ${renewalRate.toFixed(2)}, 续费人数: ${renewalCount}`);
            }

            // 检查记录是否存在
            const sqlCheckExistence = `
                SELECT COUNT(*) AS count
                FROM weekly_renewal_rates
                WHERE base_date = ? AND pk = ?
            `;
            const formattedBaseDate = formatDateToLocal(baseDate);
            const paramsCheckExistence = [formattedBaseDate, pk];
            const result = await mysqlQuery(sqlCheckExistence, paramsCheckExistence);

            if (result[0].count > 0) {
                // 更新记录
                const sqlUpdateRate = `
                    UPDATE weekly_renewal_rates
                    SET initial_count = ?, rates = ?, update_date = ?
                    WHERE base_date = ? AND pk = ?
                `;
                const paramsUpdateRate = [
                    initialCount,
                    JSON.stringify(weeklyRates),
                    utils.formatDate(new Date()),
                    formattedBaseDate,
                    pk
                ];
                await mysqlQuery(sqlUpdateRate, paramsUpdateRate);
                // console.log(`更新数据成功，基准日期: ${baseDate}`);
            } else {
                // 插入新记录
                const sqlInsertRate = `
                    INSERT INTO weekly_renewal_rates (
                        id, base_date, initial_count, rates, pk, create_date, update_date
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)
                `;
                const paramsInsertRate = [
                    utils.createId(),
                    formattedBaseDate,
                    initialCount,
                    JSON.stringify(weeklyRates),
                    pk,
                    utils.formatDate(new Date()),
                    utils.formatDate(new Date())
                ];
                await mysqlQuery(sqlInsertRate, paramsInsertRate);
                // console.log(`插入数据成功，基准日期: ${baseDate}`);
            }
        }
    } catch (error) {
        console.error('处理请求时出错:', error);
    } finally {
        const endTime = new Date();
        // console.log('结束时间:', endTime.toLocaleString());
        insertNewUserRenewals(pk)
    }
}

function countCommonElements(arr1, arr2) {
    // 使用集合来存储数组1中的元素
    const set1 = new Set(arr1);
    // 使用集合来存储数组2中的元素
    const set2 = new Set(arr2);

    // 用于计数重复元素的数量
    let count = 0;

    // 遍历数组1的集合
    for (let item of set1) {
        // 如果数组2的集合中也包含当前元素，则计数增加
        if (set2.has(item)) {
            count++;
        }
    }

    // 返回重复元素的数量
    return count;
}
// 示例使用
const array1 = [1, 2, 3, 4, 5];
const array2 = [4, 5, 6, 7, 8];
// console.log(countCommonElements(array1, array2));

//计算周续费率 (每天-第一次续订的人数)

async function calculateAndStoreWeeklyRenewalRatesNew(pk) {
    const chinaTime = new Date();
    // console.log('定时任务开始执行---计算周续费率 (每天-第一次续订的人数)--执行时间--' + chinaTime.toLocaleString())
    const startTime = new Date();
    // console.log('开始时间:', startTime.toLocaleString());

    try {
        // console.log(`处理 PK: ${pk}`);

        // 获取所有日期
        const sqlSelectAllDates = `
            SELECT DISTINCT date
            FROM new_successful_renewals
            WHERE pk = ?
            ORDER BY date
        `;
        const paramsAllDates = [pk];
        const allDatesResults = await mysqlQuery(sqlSelectAllDates, paramsAllDates);
        const allDates = allDatesResults.map(row => row.date);
        // console.log(`找到 ${allDates.length} 个基准日期。`);

        for (const baseDate of allDates) {
            // console.log(`处理基准日期: ${baseDate}`);

            const sqlSelectBaseWeek = `
                SELECT originalOrderIds
                FROM new_successful_renewals
                WHERE date = ? AND pk = ?
            `;
            const paramsBaseWeek = [baseDate, pk];
            const baseWeekResults = await mysqlQuery(sqlSelectBaseWeek, paramsBaseWeek);
            // 解析 originalOrderIds
            const baseWeekIds = new Set();
            baseWeekResults.forEach(row => {
                const ids = row.originalOrderIds.split(',');
                // console.log(ids,'ids')
                ids.forEach(id => {
                    const trimmedId = id.trim();
                    if (trimmedId) { // 过滤掉空字符串
                        baseWeekIds.add(trimmedId);
                    }
                });
            });
            // console.log(baseWeekIds.size,'baseWeekIds.size')

            const initialCount = baseWeekIds.size;
            // console.log(`基准日期 ${baseDate} 的初始人数: ${initialCount}`);

            let currentDate = new Date(baseDate);
            const weeklyRates = [];

            while (true) {
                // 移动到下一周的同一天
                currentDate.setDate(currentDate.getDate() + 7);
                const nextWeekDate = currentDate.toLocaleDateString('zh-CN', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\//g, '-');

                const sqlSelectNextWeek = `
                    SELECT originalOrderIds
                    FROM successful_renewals
                    WHERE date = ? AND pk = ?
                `;
                const paramsNextWeek = [nextWeekDate, pk];
                const nextWeekResults = await mysqlQuery(sqlSelectNextWeek, paramsNextWeek);

                if (nextWeekResults.length === 0) {
                    // console.log(`没有数据用于周开始于 ${nextWeekDate}。结束循环。`);
                    break; // 没有更多数据
                }

                const nextWeekIds = new Set();
                nextWeekResults.forEach(row => {
                    const ids = row.originalOrderIds.split(',');
                    ids.forEach(id => nextWeekIds.add(id.trim()));
                });

                const continuedRenewals = [...baseWeekIds].filter(id => nextWeekIds.has(id));

                const renewalRate = continuedRenewals.length / initialCount;
                const renewalCount = continuedRenewals.length;
                weeklyRates.push({ week: nextWeekDate, rate: renewalRate, count: renewalCount });

                // console.log(`基准日期: ${baseDate}, 周: ${nextWeekDate}, 续费率: ${renewalRate.toFixed(2)}, 续费人数: ${renewalCount}`);

            }

            // 检查记录是否存在
            const sqlCheckExistence = `
                SELECT COUNT(*) AS count
                FROM new_weekly_renewal_rates
                WHERE base_date = ? AND pk = ?
            `;
            const formattedBaseDate = formatDateToLocal(baseDate);
            const paramsCheckExistence = [formattedBaseDate, pk];
            const result = await mysqlQuery(sqlCheckExistence, paramsCheckExistence);

            if (result[0].count > 0) {
                // 更新记录
                const sqlUpdateRate = `
                    UPDATE new_weekly_renewal_rates
                    SET initial_count = ?, rates = ?, update_date = ?
                    WHERE base_date = ? AND pk = ?
                `;
                const paramsUpdateRate = [
                    initialCount,
                    JSON.stringify(weeklyRates),
                    utils.formatDate(new Date()),
                    formattedBaseDate,
                    pk
                ];
                await mysqlQuery(sqlUpdateRate, paramsUpdateRate);
                // console.log(`更新数据成功，基准日期: ${baseDate}`);
            } else {
                // 插入新记录
                const sqlInsertRate = `
                    INSERT INTO new_weekly_renewal_rates (
                        id, base_date, initial_count, rates, pk, create_date, update_date
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)
                `;
                const paramsInsertRate = [
                    utils.createId(),
                    formattedBaseDate,
                    initialCount,
                    JSON.stringify(weeklyRates),
                    pk,
                    utils.formatDate(new Date()),
                    utils.formatDate(new Date())
                ];
                await mysqlQuery(sqlInsertRate, paramsInsertRate);
                // console.log(`插入数据成功，基准日期: ${baseDate}`);
            }
        }
    } catch (error) {
        console.error('处理请求时出错:', error);
    } finally {
        const endTime = new Date();
        console.log('结束时间:', endTime.toLocaleString());
        updateDailyRefundCount(pk)
        updateConvertedAfterTrial(pk)
    }
}

//每天退费人数
async function updateDailyRefundCount(pk) {
    // 查询 daily_refund_count 为空的记录
    const fetchRatesSql = `SELECT * FROM new_weekly_renewal_rates WHERE pk = ? AND (daily_refund_count IS NULL OR daily_refund_count = '' )`;
    const fetchRatesParams = [pk];
    try {
        const rates = await mysqlQuery(fetchRatesSql, fetchRatesParams);

        for (const rate of rates) {
            const baseDate = rate.base_date;

            // 根据 base_date 查询 js_app_apple_notifications 表中的 REFUND 记录
            const fetchRefundsSql = `SELECT COUNT(*) AS refundCount FROM js_app_apple_notifications 
                                      WHERE pk = ? AND DATE(update_date) = ? AND notification_type = 'REFUND'`;
            const fetchRefundsParams = [pk, moment(baseDate).format('YYYY-MM-DD')];

            const refundResults = await mysqlQuery(fetchRefundsSql, fetchRefundsParams);
            const refundCount = refundResults[0]?.refundCount || 0;

            // 更新 daily_refund_count
            const updateSql = `UPDATE new_weekly_renewal_rates SET daily_refund_count = ? WHERE pk = ? AND base_date = ?`;
            const updateParams = [refundCount, pk, baseDate];

            await mysqlQuery(updateSql, updateParams);
        }

    } catch (error) {

    }
}

//转正率
// async function updateConvertedAfterTrial(pk) {

//     let sqlFetchRates = `SELECT * FROM new_weekly_renewal_rates WHERE pk = ? AND (converted_after_trial IS NULL OR converted_after_trial = '' OR converted_after_trial = 0)`;
//     let paramsFetchRates = [pk];

//     try {
//         const ratesResults = await mysqlQuery(sqlFetchRates, paramsFetchRates);

//         for (const rate of ratesResults) {
//             const baseDate = moment(rate.base_date).format('YYYY-MM-DD');
//             const startDate = moment(baseDate).subtract(3, 'days').startOf('day').format('YYYY-MM-DD HH:mm:ss');
//             const endDate = moment(baseDate).subtract(3, 'days').endOf('day').format('YYYY-MM-DD HH:mm:ss');

//             // Fetch INITIAL_BUY records within the calculated date range
//             let sqlInitialBuy = `SELECT * FROM js_app_apple_notifications WHERE pk = ? AND subtype = 'INITIAL_BUY' AND update_date BETWEEN ? AND ?`;
//             let paramsInitialBuy = [pk, startDate, endDate];

//             const initialBuyResults = await mysqlQuery(sqlInitialBuy, paramsInitialBuy);
//             const initialBuyCounts = {};
//             const initialBuyUserIds = {};

//             initialBuyResults.forEach(row => {
//                 const date = moment(row.update_date).add(3, 'days').format('YYYY-MM-DD');
//                 if (!initialBuyCounts[date]) {
//                     initialBuyCounts[date] = 0;
//                     initialBuyUserIds[date] = new Set();
//                 }
//                 initialBuyCounts[date]++;
//                 initialBuyUserIds[date].add(row.user_id);
//             });

//             const renewalCounts = {};
//             const renewalPromises = Object.keys(initialBuyCounts).map(async date => {
//                 const startDate = moment(date).startOf('day').format('YYYY-MM-DD HH:mm:ss');
//                 const endDate = moment(date).endOf('day').format('YYYY-MM-DD HH:mm:ss');

//                 const sqlRenewal = `SELECT * FROM js_app_apple_notifications WHERE pk = ? AND notification_type = 'DID_RENEW' AND update_date BETWEEN ? AND ? AND DATE(original_purchase_date) = DATE(?)`;
//                 const paramsRenewal = [pk, startDate, endDate, moment(date).subtract(3, 'days').format('YYYY-MM-DD')];

//                 const renewalResults = await mysqlQuery(sqlRenewal, paramsRenewal);
//                 renewalCounts[date] = renewalResults.filter(row => initialBuyUserIds[date].has(row.user_id)).length;
//             });

//             await Promise.all(renewalPromises);
//             // Calculate total renewal count after the trial period
//             const totalRenewalCount = Object.keys(initialBuyCounts).reduce((sum, date) => {
//                 return sum + (renewalCounts[date] || 0);
//             }, 0);
//             const updateBaseDate = moment(baseDate).subtract(3, 'days').format('YYYY-MM-DD');
//             const updateSql = `UPDATE new_weekly_renewal_rates SET converted_after_trial = ? WHERE base_date = ? AND pk = ?`;
//             await mysqlQuery(updateSql, [totalRenewalCount, updateBaseDate, pk]);
//         }


//     } catch (error) {
//         console.error(error);
//     }
// }
async function updateConvertedAfterTrial(pk) {
    let sqlFetchRates = `SELECT * FROM new_weekly_renewal_rates WHERE pk = ? AND (converted_after_trial IS NULL OR converted_after_trial = '' OR converted_after_trial = 0)`;
    let paramsFetchRates = [pk];

    try {
        const ratesResults = await mysqlQuery(sqlFetchRates, paramsFetchRates);

        for (const rate of ratesResults) {
            const baseDate = moment(rate.base_date).format('YYYY-MM-DD');
            // 计算完整日期，即baseDate加三天
            const completeDate = moment(baseDate).add(4, 'days');
            const currentDate = moment();

            // 如果completeDate晚于当前日期，则跳过此次计算，因为数据不完整
            if (completeDate.isAfter(currentDate)) {
                console.log(`当天 ${baseDate} 数据不完整，不计算.`);
                continue;
            }
            const startDate = moment(baseDate).startOf('day').format('YYYY-MM-DD HH:mm:ss');
            const endDate = moment(baseDate).endOf('day').format('YYYY-MM-DD HH:mm:ss');

            // Fetch INITIAL_BUY records on the baseDate with offer_discount_type 'FREE_TRIAL'
            let sqlInitialBuy = `SELECT * FROM js_app_apple_notifications WHERE pk = ? AND subtype = 'INITIAL_BUY' AND offer_discount_type = 'FREE_TRIAL' AND update_date BETWEEN ? AND ?`;
            let paramsInitialBuy = [pk, startDate, endDate];

            const initialBuyResults = await mysqlQuery(sqlInitialBuy, paramsInitialBuy);
            const originalTransactionIds = initialBuyResults.map(row => row.original_transaction_id);
            const dailyTrialCount = originalTransactionIds.length;

            const renewalCounts = {};
            const renewalPromises = originalTransactionIds.map(async originalTransactionId => {
                const futureStartDate = moment(baseDate).add(1, 'days').startOf('day').format('YYYY-MM-DD HH:mm:ss');
                const futureEndDate = moment(baseDate).add(3, 'days').endOf('day').format('YYYY-MM-DD HH:mm:ss');

                const sqlRenewal = `SELECT COUNT(*) AS renewalCount FROM new_successful_renewals WHERE pk = ? AND FIND_IN_SET(?, originalOrderIds) AND date BETWEEN ? AND ?`;

                const paramsRenewal = [pk, originalTransactionId, futureStartDate, futureEndDate];

                const [renewalResult] = await mysqlQuery(sqlRenewal, paramsRenewal);
                renewalCounts[originalTransactionId] = renewalResult.renewalCount > 0 ? 1 : 0;
            });

            await Promise.all(renewalPromises);

            // Calculate total renewal count after the trial period
            const totalRenewalCount = Object.values(renewalCounts).reduce((sum, count) => {
                return sum + count;
            }, 0);

            const updateSql = `UPDATE new_weekly_renewal_rates SET daily_trial_count = ?, converted_after_trial = ? WHERE base_date = ? AND pk = ?`;
            await mysqlQuery(updateSql, [dailyTrialCount, totalRenewalCount, baseDate, pk]);
        }
    } catch (error) {
        console.error(error);
    } finally {
        const endTime = new Date();
        console.log('计算转正结束时间:', endTime.toLocaleString());

    }
}
function formatDateToLocal(date) {
    const localDate = new Date(date);
    localDate.setMinutes(localDate.getMinutes() - localDate.getTimezoneOffset());
    return localDate.toISOString().split('T')[0];
}
async function mysqlQueryMOMO(sql, arr) {

    return new Promise((resolve, reject) => {
        poolmomo.query(sql, arr, (error, results, fields) => {
            if (error) {
                return reject(error);
            }
            resolve(results);
        });
    });
}
async function mysqlQuery(sql, arr) {

    return new Promise((resolve, reject) => {
        pool.query(sql, arr, (error, results, fields) => {
            if (error) {
                return reject(error);
            }
            resolve(results);
        });
    });
}
async function updateCommentTypeNames() {
    try {
        const selectQuery = `
            SELECT * FROM js_app_comment WHERE is_reply = 1 AND star_rating < 5 AND template_content_id IS NOT NULL and type_id is null
        `;

        const results = await mysqlQuery(selectQuery, []);

        for (let result of results) {
            // 从 js_app_comment_template 表中获取 type_id
            const templateQuery = `
                SELECT type_id FROM js_app_comment_template WHERE id = ?
            `;
            const templateResults = await mysqlQuery(templateQuery, [result.template_content_id]);

            if (templateResults.length > 0) {
                const typeId = templateResults[0].type_id;

                // 使用 type_id 从 js_app_comment_type 表中获取 comment_type
                const typeQuery = `
                    SELECT comment_type FROM js_app_comment_type WHERE id = ?
                `;
                const typeResults = await mysqlQuery(typeQuery, [typeId]);

                if (typeResults.length > 0) {
                    const commentType = typeResults[0].comment_type;

                    // 更新 js_app_comment 表中的 type_name
                    const updateQuery = `
                        UPDATE js_app_comment 
                        SET type_id = ?
                        WHERE id = ?
                    `;
                    await mysqlQuery(updateQuery, [typeId, result.id]);
                }
            }
        }

        // console.log('All comments type_name updated successfully');

    } catch (error) {
        console.error('Error updating comment type names:', error);
    }
}

//tokens 的定时分表
async function scheduleTokenDistribution() {
    const chinaTime = new Date();
    try {
        const today = moment().tz('Asia/Shanghai').format('YYYY-MM-DD');
        // console.log(today)
        const processedQuery = `
            SELECT DISTINCT date, type 
            FROM token_usage_per_day
        `;

        const processed = await mysqlQuery(processedQuery, []);

        const processedSet = new Set(processed.map(row => `${row.date}-${row.type}`));

        const query = `
            SELECT 
                date, 
                type, 
                SUM(prompt_tokens) AS total_prompt_tokens, 
                SUM(completion_tokens) AS total_completion_tokens, 
                SUM(total_tokens) AS total_tokens,
                MIN(model) AS model
            FROM ai_tokens 
            WHERE date < ?
            GROUP BY date, type
        `;

        const result = await mysqlQuery(query, [today]);
        for (const row of result) {

            if (processedSet.has(`${row.date}-${row.type}`)) {
                continue;
            }

            let prompt_dollar = 0;
            let completion_dollar = 0;

            if (row.model === 'gpt-4o-2024-11-20') {
                // 特定模型的算法
                prompt_dollar = (row.total_prompt_tokens / 1000) * 0.0025;
                completion_dollar = (row.total_completion_tokens / 1000) * 0.01;
            } else {
                // 其他模型的算法 (假设为示例)
                prompt_dollar = (row.total_prompt_tokens / 1000) * 0.0025;
                completion_dollar = (row.total_completion_tokens / 1000) * 0.01;
            }

            const total_dollar = prompt_dollar + completion_dollar;

            const insertQuery = `
                INSERT INTO token_usage_per_day (
                    id, date, type, 
                    prompt_tokens, completion_tokens, total_tokens, 
                    prompt_dollar, completion_dollar, total_dollar,
                    model, update_date
                )
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE
                prompt_tokens = VALUES(prompt_tokens),
                completion_tokens = VALUES(completion_tokens),
                total_tokens = VALUES(total_tokens),
                prompt_dollar = VALUES(prompt_dollar),
                completion_dollar = VALUES(completion_dollar),
                total_dollar = VALUES(total_dollar),
                model = VALUES(model),
                update_date = VALUES(update_date)
            `;

            await mysqlQuery(insertQuery, [
                utils.createId(),
                row.date,
                row.type,
                row.total_prompt_tokens,
                row.total_completion_tokens,
                row.total_tokens,
                prompt_dollar,
                completion_dollar,
                total_dollar,
                row.model,
                utils.formatDate(new Date())
            ]);
        }

        // console.log('Token distribution scheduled successfully.');
    } catch (error) {
        console.error('Error scheduling token distribution:', error);
    }
}
async function scheduleCalorieTokenDistribution() {
    try {
        // 1. 获取今天日期（防止重复统计当天）
        const today = moment().tz('Asia/Shanghai').format('YYYY-MM-DD');

        // 2. 查已处理的历史(date+type)
        const processedQuery = `
            SELECT DISTINCT date, type 
            FROM Calorie_token_usage_per_day
        `;
        const processed = await mysqlQuery(processedQuery, []);
        const processedSet = new Set(processed.map(row => `${row.date}-${row.type}`));

        // 3. 针对每个表分析数据并插入
        const calorieSources = [
            { table: 'Calorie_exercise_api_logs', type: 'exercise' },
            { table: 'Calorie_food_text_api_logs', type: 'food_text' },
            { table: 'Calorie_food_scan_api_logs', type: 'food_scan' }
        ];

        // 4. 循环三个表，每个都出一份统计数据
        for (const source of calorieSources) {
            const query = `
                SELECT 
                    date,
                    SUM(prompt_tokens) AS total_prompt_tokens,
                    SUM(completion_tokens) AS total_completion_tokens,
                    SUM(total_tokens) AS total_tokens,
                    MIN(model) AS model
                FROM ${source.table}
                WHERE date < ?
                GROUP BY date
            `;
            const result = await mysqlQuery(query, [today]);
            for (const row of result) {
                // 唯一去重
                if (processedSet.has(`${row.date}-${source.type}`)) {
                    continue;
                }
                // token消耗转美金
                let prompt_dollar = 0, completion_dollar = 0;
                if (row.model === 'gpt-4o-2024-11-20') {
                    prompt_dollar = (row.total_prompt_tokens / 1000) * 0.0025;
                    completion_dollar = (row.total_completion_tokens / 1000) * 0.01;
                } else {
                    prompt_dollar = (row.total_prompt_tokens / 1000) * 0.0025;
                    completion_dollar = (row.total_completion_tokens / 1000) * 0.01;
                }
                const total_dollar = prompt_dollar + completion_dollar;
                // 入库（幂等插入）
                const insertQuery = `
                    INSERT INTO Calorie_token_usage_per_day (
                        id, date, type,
                        prompt_tokens, completion_tokens, total_tokens,
                        prompt_dollar, completion_dollar, total_dollar,
                        model, update_date
                    )
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    ON DUPLICATE KEY UPDATE
                        prompt_tokens = VALUES(prompt_tokens),
                        completion_tokens = VALUES(completion_tokens),
                        total_tokens = VALUES(total_tokens),
                        prompt_dollar = VALUES(prompt_dollar),
                        completion_dollar = VALUES(completion_dollar),
                        total_dollar = VALUES(total_dollar),
                        model = VALUES(model),
                        update_date = VALUES(update_date)
                `;
                await mysqlQuery(insertQuery, [
                    utils.createId(),
                    row.date,
                    source.type,
                    row.total_prompt_tokens || 0,
                    row.total_completion_tokens || 0,
                    row.total_tokens || 0,
                    prompt_dollar,
                    completion_dollar,
                    total_dollar,
                    row.model,
                    utils.formatDate(new Date())
                ]);
            }
        }
    } catch (error) {
        console.error('Error scheduling calorie token distribution:', error);
    }
}
async function scheduleNoteTokenDistribution() {
    try {
        const today = moment().tz('Asia/Shanghai').format('YYYY-MM-DD');

        // 已处理的(date, type="llm")组合
        const processedQuery = `
            SELECT DISTINCT date 
            FROM ainote_token_usage_per_day
            WHERE type = ?
        `;
        const processed = await mysqlQuery(processedQuery, ['llm']);
        const processedSet = new Set(processed.map(row => row.date));

        // 只分组date，后续插入type统一为"llm"
        const query = `
            SELECT 
                date, 
                SUM(prompt_tokens) AS total_prompt_tokens, 
                SUM(completion_tokens) AS total_completion_tokens, 
                SUM(total_tokens) AS total_tokens,
                MIN(model) AS model
            FROM ainote_log
            WHERE date < ?
            GROUP BY date
        `;

        const result = await mysqlQuery(query, [today]);

        for (const row of result) {
            if (processedSet.has(row.date)) {
                continue; // 已处理过llm类型
            }

            let prompt_dollar = 0;
            let completion_dollar = 0;

            if (row.model === 'gpt-4o-2024-11-20') {
                prompt_dollar = (row.total_prompt_tokens / 1000) * 0.0025;
                completion_dollar = (row.total_completion_tokens / 1000) * 0.01;
            } else {
                prompt_dollar = (row.total_prompt_tokens / 1000) * 0.0025;
                completion_dollar = (row.total_completion_tokens / 1000) * 0.01;
            }

            const total_dollar = prompt_dollar + completion_dollar;

            const insertQuery = `
                INSERT INTO ainote_token_usage_per_day (
                    id, date, type, 
                    prompt_tokens, completion_tokens, total_tokens, 
                    prompt_dollar, completion_dollar, total_dollar,
                    model, update_date
                )
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE
                prompt_tokens = VALUES(prompt_tokens),
                completion_tokens = VALUES(completion_tokens),
                total_tokens = VALUES(total_tokens),
                prompt_dollar = VALUES(prompt_dollar),
                completion_dollar = VALUES(completion_dollar),
                total_dollar = VALUES(total_dollar),
                model = VALUES(model),
                update_date = VALUES(update_date)
            `;

            await mysqlQuery(insertQuery, [
                utils.createId(),
                row.date,
                'llm', // type始终为llm
                row.total_prompt_tokens,
                row.total_completion_tokens,
                row.total_tokens,
                prompt_dollar,
                completion_dollar,
                total_dollar,
                row.model,
                utils.formatDate(new Date())
            ]);
        }

        // console.log('Token distribution scheduled successfully.');
    } catch (error) {
        console.error('Error scheduling token distribution:', error);
    }
}




// 报警规则配置
const ALERT_THRESHOLD = 0.3; // 30%变化率阈值

// 时间区间格式化函数
function formatTimeRange(startHour) {
    const endHour = startHour + 2;
    return `${String(startHour).padStart(2, '0')}:00-${String(endHour).padStart(2, '0')}:00`;
}

// 根据当前时间构造两个连续完整的两小时窗口：
// 当前窗口表示上一个完整的两小时区间；前一窗口为当前窗口之前的两小时区间
function getTwoHourWindows() {
    const now = new Date();

    // 计算最近的偶数整点（例如 14:00、22:00 等）
    // 注意：如果当前时间正好在偶数整点（分钟、秒、毫秒全为0），则 boundary === now
    const boundary = new Date(
        now.getFullYear(),
        now.getMonth(),
        now.getDate(),
        now.getHours() - (now.getHours() % 2)
    );

    // 如果当前时间正好对齐（例如 14:00:00.000），则完成的窗口是 boundary 前的两小时区间
    // 否则，也以 boundary 为当前窗口的结束时间（数据还未完整，实际环境下建议确保定时任务在对齐时刻执行）
    const currentWindow = {
        start: new Date(boundary.getTime() - 2 * 3600 * 1000),
        end: boundary
    };

    const prevWindow = {
        start: new Date(currentWindow.start.getTime() - 2 * 3600 * 1000),
        end: currentWindow.start
    };

    return { currentWindow, prevWindow };
}
// 统计 ios_version 占比（只统计刷新类型 reflash 的记录）
async function getIosVersionDistribution(startTime, endTime) {
    const sql = `
        SELECT
            ios_version,
            COUNT(*) AS count
        FROM wa_event_tracking
        WHERE
            eventTimeMillis >= ?
            AND eventTimeMillis < ?
            AND type = 'reflash'
        GROUP BY ios_version
    `;

    try {
        const results = await utils.query(sql, [startTime.getTime(), endTime.getTime()]);
        // const total = results.reduce((sum, cur) => sum + cur.count, 0);
        // const distribution = {};
        // results.forEach(item => {
        //     distribution[item.ios_version] = ((item.count / total) * 100).toFixed(1) + '%';
        // });
        // console.log(distribution, 'ios_version_distribution');
        return results;
    } catch (error) {
        console.log('ios_version分布统计失败:', error)
        console.error('ios_version分布统计失败:', error);
        return null;
    }
}
// 获取统计数据
async function getStatistic(startTime, endTime) {
    const sql = `
        SELECT 
            type,
            COUNT(*) AS count 
        FROM wa_event_tracking 
        WHERE 
            eventTimeMillis >= ? 
            AND eventTimeMillis < ? 
            AND type IN ('web', 'reflash')
        GROUP BY type
    `;

    try {
        const results = await utils.query(sql, [startTime.getTime(), endTime.getTime()]);
        console.log(results, 'results')
        return results.reduce((acc, cur) => {
            acc[cur.type] = cur.count;
            return acc;
        }, {});
    } catch (error) {
        console.error('统计查询失败:', error);
        return null;
    }
}

// 报警检查主逻辑
async function checkAlert() {
    if (process.env.HOSTNAME.includes("dev01-jp")) {
        const chinaTime = new Date();
        console.log("每两小时执行一次 wa上报----" + chinaTime.toLocaleString());

        // 1. 获取当前的两个时间窗口（当前窗口为上一个完整两小时区间，前一窗口为其前一个两小时区间）
        const { currentWindow, prevWindow } = getTwoHourWindows();


        // 2. 获取统计数据
        const [currentStat, prevStat] = await Promise.all([
            getStatistic(currentWindow.start, currentWindow.end),
            getStatistic(prevWindow.start, prevWindow.end)
        ]);


        // 3. 数据校验
        if (!currentStat || !prevStat) {
            console.log('数据不完整，跳过检查');
            return;
        }
        if (!currentStat.web || !prevStat.web) {
            console.log('存在空访问量数据，跳过检查');
            return;
        }

        // 4. 计算刷新率
        const currentRate = currentStat.reflash / currentStat.web;
        console.log(currentRate, 'currentRate');
        const prevRate = prevStat.reflash / prevStat.web;
        console.log(prevRate, 'prevRate');


        // 新增：保存统计数据到新表
        const statData = await aggregateStatistics(currentWindow.start, currentWindow.end);

        if (statData) {
            const insertSql = `
                INSERT INTO wa_event_statistics (id,
                    start_time, end_time,
                    web_count, reflash_count, mode_count,
                    device_dist, ios_version_dist, lang_dist, region_dist,current_refresh_rate,create_date,update_date
                ) VALUES (?,?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            `;
            await utils.query(insertSql, [
                utils.createId(),
                statData.start_time,
                statData.end_time,
                statData.web_count,
                statData.reflash_count,
                statData.mode_count,
                JSON.stringify(statData.device_dist),
                JSON.stringify(statData.ios_version_dist),
                JSON.stringify(statData.lang_dist),
                JSON.stringify(statData.region_dist),
                (currentRate * 100).toFixed(2),
                utils.formatDate(new Date()),
                utils.formatDate(new Date())
            ]);
        }


        // 5. 变化率计算（防止除以0）
        const rateChange = prevRate !== 0
            ? (currentRate - prevRate) / prevRate
            : currentRate > 0 ? Infinity : 0;
        console.log(`刷新率变化: ${(rateChange * 100).toFixed(1)}%`)


        // ------------------ 新增 回落通知逻辑 开始 ------------------
        const DROPDOWN_ALERT_THRESHOLD = 0.05; // 新增：回落阈值，5%
        if (
            prevRate > 0.06 &&                     // 上一时段刷新率高于6%
            currentRate < DROPDOWN_ALERT_THRESHOLD // 当前低于5%
        ) {
            // 新增内容 START
            const timeRange = formatTimeRange(currentWindow.start.getHours());
            console.log(`✅ 刷新率已回落于 ${timeRange} 时间段：`);
            console.log(`刷新率：由${(prevRate * 100).toFixed(1)}%回落到${(currentRate * 100).toFixed(1)}%`);
            let token = await msgUtils.getTenantAccessToken();
            let msg_type = "interactive";
            let id = `oc_43a0f6d39cffe73d48ac5dd67ac0fba6`;

            let content = ` 刷新率回落通知：\n时间:${timeRange}\n上时段刷新率:${(prevRate * 100).toFixed(1)}%，本时段刷新率:${(currentRate * 100).toFixed(1)}%（低于5%）\n如有异常波动可关注。`
            let at = `<at id="g1eff8g3">(产品)</at><at id="8d333cd2"></at><at id="5bfbfa32">(测试)</at><at id="c47a29d7">(测试)</at>`

            let contentMsg = {
                config: { wide_screen_mode: true, update_multi: true },
                i18n_elements: {
                    zh_cn: [
                        { tag: "markdown", content: `${at}` },
                        { tag: "hr" },
                        { tag: "markdown", content: `${content}` }
                    ]
                },
                i18n_header: {
                    zh_cn: {
                        title: {
                            tag: "plain_text",
                            content: "✅ 刷新率已回落至安全范围"
                        },
                        template: "green"
                    }
                }
            };
            let jsonString = JSON.stringify(contentMsg);
            await msgUtils.sendMessage(id, msg_type, jsonString, token);
            // 新增内容 END
        }


        // 6. 报警判断
        console.log(rateChange, 'rateChange')
        console.log(ALERT_THRESHOLD, 'ALERT_THRESHOLD')
        console.log(Number(currentRate) > 0.06, 'Number(currentRate) > 0.06')
        console.log(rateChange > ALERT_THRESHOLD, 'rateChange > ALERT_THRESHOLD')
        if (rateChange > ALERT_THRESHOLD && Number(currentRate) > 0.06) {
            console.log('wa预警触发')
            // 统计 ios_version 的分布情况wa_daily_app_open_summary
            const iosDistribution = await getIosVersionDistribution(currentWindow.start, currentWindow.end);
            console.log(iosDistribution, 'iosDistribution')
            iosDistribution.sort((a, b) => b.count - a.count);
            console.log('进2')
            const maxItems = 10;
            const filteredData = iosDistribution.slice(0, maxItems);



            // 使用当前窗口结束时间对应的整点来格式化时间区间。
            // 例如当前窗口为 12:00-14:00，则 formatTimeRange(12) 返回 "12:00-14:00"
            const timeRange = formatTimeRange(currentWindow.start.getHours());
            console.log(`🚨 警报触发于 ${timeRange} 时间段：`);
            console.log(`刷新率变化: ${(rateChange * 100).toFixed(1)}%`);
            console.log(`当前刷新率: ${(currentRate * 100).toFixed(1)}%`);
            console.log(`前次刷新率: ${(prevRate * 100).toFixed(1)}%`);
            console.log('警报内容: 注意！web 可能不稳定，刷新有突发增长的情况');

            let token = await msgUtils.getTenantAccessToken();
            let msg_type = "interactive";
            let id = `oc_43a0f6d39cffe73d48ac5dd67ac0fba6`;
            let content = ` 警报触发于:${timeRange}\n 当前设定预警阈值:${ALERT_THRESHOLD}\n 刷新率变化:${(rateChange * 100).toFixed(1)}%\n 前次时段进入web人次:${prevStat.web},刷新人次:${prevStat.reflash},刷新率:${(prevRate * 100).toFixed(1)}%\n 当前时段进入web人次:${currentStat.web},刷新人次:${currentStat.reflash},刷新率:${(currentRate * 100).toFixed(1)}%`
            let at = `<at id="g1eff8g3">(产品)</at><at id="8d333cd2"></at><at id="5bfbfa32">(测试)</at><at id="c47a29d7">(测试)</at>`


            // 查询最近6条刷新率记录（按创建时间降序排序）
            const recentRefreshQuery = `
  SELECT *,
         DATE_FORMAT(create_date, '%m-%d %H:%i') AS formatted_create_date
  FROM wa_event_statistics
  ORDER BY create_date DESC
  LIMIT 12
`;

            let recentRefreshRows = await utils.query(recentRefreshQuery);
            // 保证在折线图中数据按时间从早到晚排列
            recentRefreshRows.reverse();
            const pad = (n) => n.toString().padStart(2, '0');
            const lineData = recentRefreshRows.map(row => {
                const start = new Date(row.start_time);
                const end = new Date(row.end_time);
                const timeRange = `${pad(start.getHours())}-${pad(end.getHours())}`;
                return {
                    time: timeRange,
                    current_refresh_rate: row.current_refresh_rate,
                    web_count: row.web_count,
                    reflash_count: row.reflash_count,
                    mode_count: row.mode_count,
                    tooltipText: `时间: ${timeRange}\n进入web人次: ${row.web_count}\n刷新次数: ${row.reflash_count}\nmode次数: ${row.mode_count}`
                };
            });


            let contentMsg = {
                config: { wide_screen_mode: true, update_multi: true },
                i18n_elements: {
                    zh_cn: [
                        {
                            tag: "markdown",
                            content: `${at}`,
                            text_align: "left",
                            text_size: "normal"
                        },
                        {
                            tag: "hr"
                        },
                        {
                            tag: "markdown",
                            content: `${content}`,
                            text_align: "left",
                            text_size: "normal"
                        },
                        {
                            tag: "hr"
                        },
                        {
                            tag: "chart",
                            chart_spec: {
                                type: "bar",
                                title: { text: "刷新的系统版本分布" },
                                data: {
                                    values: filteredData.map((item) => ({
                                        type: "iOS",
                                        ios_version: item.ios_version,
                                        count: item.count
                                    }))
                                },
                                xField: ["ios_version", "type"],
                                yField: "count",
                                seriesField: "type",
                                legends: { visible: false, orient: "bottom" }
                            },
                            preview: true,
                            color_theme: "brand",
                            height: "auto"
                        },
                        {
                            tag: "chart",
                            chart_spec: {
                                type: "line",
                                title: { text: "刷新趋势折线图" },
                                data: {
                                    values: lineData
                                },
                                xField: "time",
                                yField: "current_refresh_rate",
                                // 此处不再配置 tooltip
                                legends: { visible: false }
                            },
                            preview: true,
                            color_theme: "brand",
                            height: "auto"
                        },
                        {
                            tag: "person_list",
                            persons: [
                                {
                                    "id": "g1eff8g3"
                                },
                                {
                                    "id": "8d333cd2"
                                },
                                {
                                    "id": "5bfbfa32"
                                },
                                {
                                    "id": "c47a29d7"
                                }
                            ],
                            size: "small",
                            show_avatar: true,
                            show_name: false
                        }
                    ]
                },
                i18n_header: {
                    zh_cn: {
                        title: {
                            tag: "plain_text",
                            content: "🚨 警报！web 可能不稳定，刷新有突发增长的情况"
                        },
                        template: "red"
                    }
                }
            };
            let jsonString = JSON.stringify(contentMsg);
            await msgUtils.sendMessage(id, msg_type, jsonString, token);
        }
    }
}

//汇总刷新率上报-两小时
/**
 * 统计指定时间窗口内的多维数据
 * @param {Date} startTime 统计开始时间
 * @param {Date} endTime 统计结束时间
 * @returns {Promise<Object>} 聚合统计结果
 */
async function aggregateStatistics(startTime, endTime) {
    // 1. 统计各类型数量（包含 web、reflash、mode，不做修改）
    const typeSql = `
        SELECT 
            type,
            COUNT(*) AS count
        FROM wa_event_tracking 
        WHERE 
            eventTimeMillis >= ? 
            AND eventTimeMillis < ?
            AND type IN ('web', 'reflash', 'mode')
        GROUP BY type
    `;

    // 2. 统计设备分布（排除 type 为 web 的记录）
    const deviceSql = `
        SELECT 
            device,
            COUNT(*) AS count
        FROM wa_event_tracking 
        WHERE 
            eventTimeMillis >= ? 
            AND eventTimeMillis < ?
            AND type = 'reflash'
        GROUP BY device
    `;

    // 3. 统计 iOS 版本分布（排除 type 为 web 的记录，同时排除 ios_version 为空的记录）
    const iosVersionSql = `
        SELECT 
            ios_version,
            COUNT(*) AS count
        FROM wa_event_tracking 
        WHERE 
            eventTimeMillis >= ? 
            AND eventTimeMillis < ?
            AND ios_version IS NOT NULL
            AND type = 'reflash'
        GROUP BY ios_version
    `;

    // 4. 统计语言分布（排除 type 为 web 的记录）
    const langSql = `
        SELECT 
            lang,
            COUNT(*) AS count
        FROM wa_event_tracking 
        WHERE 
            eventTimeMillis >= ? 
            AND eventTimeMillis < ?
            AND type = 'reflash'
        GROUP BY lang
    `;

    // 5. 统计地区分布（排除 type 为 web 的记录）
    const regionSql = `
        SELECT 
            region,
            COUNT(*) AS count
        FROM wa_event_tracking 
        WHERE 
            eventTimeMillis >= ? 
            AND eventTimeMillis < ?
            AND type = 'reflash'
        GROUP BY region
    `;

    try {
        // 并行执行所有统计查询
        const [
            typeResults,
            deviceResults,
            iosVersionResults,
            langResults,
            regionResults
        ] = await Promise.all([
            utils.query(typeSql, [startTime.getTime(), endTime.getTime()]),
            utils.query(deviceSql, [startTime.getTime(), endTime.getTime()]),
            utils.query(iosVersionSql, [startTime.getTime(), endTime.getTime()]),
            utils.query(langSql, [startTime.getTime(), endTime.getTime()]),
            utils.query(regionSql, [startTime.getTime(), endTime.getTime()])
        ]);

        // 转换类型统计结果
        const typeStats = { web: 0, reflash: 0, mode: 0 };
        typeResults.forEach(item => {
            typeStats[item.type] = item.count;
        });

        // 转换设备分布为 JSON 对象
        const deviceDist = {};
        deviceResults.forEach(item => {
            deviceDist[item.device] = item.count;
        });

        // 转换 iOS 版本分布
        const iosVersionDist = {};
        iosVersionResults.forEach(item => {
            iosVersionDist[item.ios_version] = item.count;
        });

        // 转换语言分布
        const langDist = langResults.reduce((acc, cur) => {
            acc[cur.lang] = cur.count;
            return acc;
        }, {});

        // 转换地区分布
        const regionDist = regionResults.reduce((acc, cur) => {
            acc[cur.region] = cur.count;
            return acc;
        }, {});

        return {
            start_time: startTime,
            end_time: endTime,
            web_count: typeStats.web,
            reflash_count: typeStats.reflash,
            mode_count: typeStats.mode,
            device_dist: deviceDist,
            ios_version_dist: iosVersionDist,
            lang_dist: langDist,
            region_dist: regionDist
        };
    } catch (error) {
        console.error('聚合统计失败:', error);
        return null;
    }
}



//汇总刷新率上报-每天
async function aggregateDailySummary() {
    // 假设统计前一天的数据
    const summaryDate = moment().subtract(1, 'day').format('YYYY-MM-DD');
    // 获取当天开始和结束的毫秒级时间戳，用于直接与 eventTimeMillis 进行比较
    const startTime = moment(summaryDate).startOf('day').valueOf();   // 毫秒级开始时间
    const endTime = moment(summaryDate).endOf('day').valueOf();         // 毫秒级结束时间

    // ① 统计日活数（dau）：通过 DISTINCT uuid 统计当天的活跃用户数
    const dauSql = `
        SELECT COUNT(DISTINCT uuid) AS dau 
        FROM wa_event_tracking
        WHERE eventTimeMillis BETWEEN ? AND ?
    `;
    const dauResult = await utils.query(dauSql, [startTime, endTime]);
    const dau = dauResult[0].dau;

    // ② 统计各类型上报次数（web, reflash, mode）
    const typeSql = `
        SELECT type, COUNT(*) AS count
        FROM wa_event_tracking
        WHERE eventTimeMillis BETWEEN ? AND ?
          AND type IN ('web', 'reflash', 'mode')
        GROUP BY type
    `;
    const typeResults = await utils.query(typeSql, [startTime, endTime]);
    // 初始化各类型次数为 0
    let web_count = 0, reflash_count = 0, mode_count = 0;
    typeResults.forEach(item => {
        if (item.type === 'web') {
            web_count = item.count;
        } else if (item.type === 'reflash') {
            reflash_count = item.count;
        } else if (item.type === 'mode') {
            mode_count = item.count;
        }
    });

    // ③ 计算当天刷新率：当 web_count 为 0 时设置刷新率为 0
    let refresh_rate = 0;
    if (web_count > 0) {
        refresh_rate = reflash_count / web_count;
    }
    // 如果你期望以百分比方式存储，也可以乘以 100 保存

    // ④ 统计其它各维度分布：设备、ios版本、语言、地区分布
    async function getDistribution(field) {
        const sql = `
            SELECT ${field} AS field, COUNT(*) AS count
            FROM wa_event_tracking 
            WHERE eventTimeMillis BETWEEN ? AND ?
            GROUP BY ${field}
        `;
        const results = await utils.query(sql, [startTime, endTime]);
        // 组合成形如 { value1: count, value2: count, ... } 的对象
        return results.reduce((acc, cur) => {
            acc[cur.field] = cur.count;
            return acc;
        }, {});
    }
    const deviceDistribution = await getDistribution('device');
    const iosVersionDistribution = await getDistribution('ios_version');
    const langDistribution = await getDistribution('lang');
    const regionDistribution = await getDistribution('region');

    // ⑤ 将以上统计数据插入到汇总表中，使用 ON DUPLICATE KEY UPDATE 以避免插入重复日期
    const insertSql = `
        INSERT INTO wa_event_daily_summary (
            summary_date, dau, web_count, reflash_count, mode_count, refresh_rate,
            device_distribution, ios_version_distribution, lang_distribution, region_distribution
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE 
            dau = VALUES(dau),
            web_count = VALUES(web_count),
            reflash_count = VALUES(reflash_count),
            mode_count = VALUES(mode_count),
            refresh_rate = VALUES(refresh_rate),
            device_distribution = VALUES(device_distribution),
            ios_version_distribution = VALUES(ios_version_distribution),
            lang_distribution = VALUES(lang_distribution),
            region_distribution = VALUES(region_distribution),
            update_date = CURRENT_TIMESTAMP;
    `;

    await utils.query(insertSql, [
        summaryDate,
        dau,
        web_count,
        reflash_count,
        mode_count,
        refresh_rate,
        JSON.stringify(deviceDistribution),
        JSON.stringify(iosVersionDistribution),
        JSON.stringify(langDistribution),
        JSON.stringify(regionDistribution)
    ]);

    console.log('每日数据汇总已记录，日期：', summaryDate);
}

//汇总每天每人启动app
async function dailyAppOpenSummaryJob() {
    try {
        // 获取当前日期（格式：YYYY-MM-DD）
        const currentDate = moment().format('YYYY-MM-DD');

        // 查询当前日期之前的数据（防止当天数据不完整）
        const sqlSelect = `
      SELECT 
        date, 
        COUNT(uuid) AS count, 
        SUM(cold_start + warm_start) AS start_count, 
        SUM(cold_start) AS cold_start_count, 
        SUM(warm_start) AS warm_start_count
      FROM wa_daily_app_open_stats
      WHERE date < ?
      GROUP BY date
    `;

        const results = await mysqlQuery(sqlSelect, [currentDate]);

        if (results.length === 0) {
            console.log(`dailyAppOpenSummaryJob - [${currentDate}之前]：无数据需要汇总`);
            return;
        }

        // 当前时间字符串，用于记录创建或更新的日期
        const now = utils.formatDate(new Date());

        for (const row of results) {
            // 防止除以0，当count为0时返回0
            const count = row.count;
            // 计算后截取两位小数，不进行四舍五入
            const avgStartCount = count > 0 ? Math.floor((row.start_count / count) * 100) / 100 : 0;
            const avgColdStartCount = count > 0 ? Math.floor((row.cold_start_count / count) * 100) / 100 : 0;
            const avgWarmStartCount = count > 0 ? Math.floor((row.warm_start_count / count) * 100) / 100 : 0;

            const summarySql = `
        INSERT INTO wa_daily_app_open_summary 
          (id, date, count, start_count, cold_start_count, warm_start_count, avg_start_count, avg_cold_start_count, avg_warm_start_count, create_date, update_date)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
          count = VALUES(count),
          start_count = VALUES(start_count),
          cold_start_count = VALUES(cold_start_count),
          warm_start_count = VALUES(warm_start_count),
          avg_start_count = VALUES(avg_start_count),
          avg_cold_start_count = VALUES(avg_cold_start_count),
          avg_warm_start_count = VALUES(avg_warm_start_count),
          update_date = VALUES(update_date)
      `;
            await mysqlQuery(summarySql, [
                utils.createId(),
                row.date,
                count,
                row.start_count,
                row.cold_start_count,
                row.warm_start_count,
                avgStartCount,
                avgColdStartCount,
                avgWarmStartCount,
                now,
                now
            ]);
            console.log(`dailyAppOpenSummaryJob - 已汇总日期 ${row.date} 的数据`);
        }
        console.log(`dailyAppOpenSummaryJob - 定时任务完成：已汇总 ${results.length} 天的数据（仅统计 ${currentDate} 之前的数据）`);
    } catch (error) {
        console.error("dailyAppOpenSummaryJob - 定时任务执行出错：", error);
    }
}

//汇总每小时冷热启动次数
async function aggregateHistoricalData() {
    // SQL 中通过 NOW() 与 DATE_FORMAT 截断到整点，从而排除当前小时的数据
    const sql = `
        INSERT INTO aggregated_app_open_stats (
            hour_start, 
            total_cold_starts, 
            total_warm_starts, 
            user_count,
            per_user_starts,
            per_user_cold_starts,
            per_user_warm_starts
        )
        SELECT
            DATE_FORMAT(update_date, '%Y-%m-%d %H:00') AS hour_start,
            SUM(cold_start) AS total_cold_starts,
            SUM(warm_start) AS total_warm_starts,
            COUNT(DISTINCT uuid) AS user_count,
            (SUM(cold_start) + SUM(warm_start)) / COUNT(DISTINCT uuid) AS per_user_starts,
            SUM(cold_start) / COUNT(DISTINCT uuid) AS per_user_cold_starts,
            SUM(warm_start) / COUNT(DISTINCT uuid) AS per_user_warm_starts
        FROM
            wa_daily_app_open_stats
        WHERE
            update_date < DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
        GROUP BY
            DATE_FORMAT(update_date, '%Y-%m-%d %H:00');
    `;
    try {
        const result = await mysqlQuery(sql, []);
        console.log('Historical data aggregation completed:', result);
    } catch (error) {
        console.error('Error aggregating historical data:', error);
    }
}

async function aggregateHourly() {
    //每小时01分触发  wa启动次数汇总
    if (process.env.HOSTNAME.includes("dev01-jp")) {
        console.log('执行24小时内(时段)启动App次数')
        // 获取当前时间（任务在每小时的01分执行）
        const currentTime = new Date();
        // 将时间对齐到本小时的整点
        // 然后减去1小时即可得出「上一小时」的起始时间
        const startHour = new Date(
            new Date(currentTime).setMinutes(0, 0, 0) - 3600000
        );
        // 本小时的起始时间
        const currentHour = new Date(
            new Date(currentTime).setMinutes(0, 0, 0)
        );

        // 转毫秒值，用于与 event_timestamp 做数值比较
        const startHourMs = startHour.getTime();       // 上一小时起始时间戳
        const currentHourMs = currentHour.getTime();   // 本小时起始时间戳

        // 格式化上一小时（仅用于日志输出，数据库插入的 hour_start 直接用 SQL 函数）
        const formattedStartHour = moment(startHour).format("YYYY-MM-DD HH:00");
        console.log("Now:", currentTime, "startHour:", startHour, "formatted:", formattedStartHour);

        // 这里按照新表 wa_app_open_log 的 event_timestamp 来进行分组聚合
        // 由于 event_timestamp 为毫秒级，需要在 MySQL 里 /1000 后使用 FROM_UNIXTIME 转化为 datetime。
        const sql = `
        INSERT INTO aggregated_app_open_stats (
            hour_start, 
            total_cold_starts, 
            total_warm_starts, 
            user_count,
            per_user_starts,
            per_user_cold_starts,
            per_user_warm_starts,
            created_at, 
            updated_at
        )
        SELECT
            DATE_FORMAT(
              FROM_UNIXTIME(event_timestamp / 1000), 
              '%Y-%m-%d %H:00'
            ) AS hour_start,
            SUM(cold_start) AS total_cold_starts,
            SUM(warm_start) AS total_warm_starts,
            COUNT(DISTINCT uuid) AS user_count,
            (SUM(cold_start) + SUM(warm_start)) / COUNT(DISTINCT uuid) AS per_user_starts,
            SUM(cold_start) / COUNT(DISTINCT uuid) AS per_user_cold_starts,
            SUM(warm_start) / COUNT(DISTINCT uuid) AS per_user_warm_starts,
            NOW() AS created_at,
            NOW() AS updated_at
        FROM
            wa_app_open_log
        WHERE
            event_timestamp >= ? 
            AND event_timestamp < ?
        GROUP BY
            DATE_FORMAT(FROM_UNIXTIME(event_timestamp / 1000), '%Y-%m-%d %H:00');
    `;

        try {
            // 在上一小时到当前小时这个区间内的数据进行聚合
            const result = await mysqlQuery(sql, [startHourMs, currentHourMs]);
            console.log(`Hourly aggregation for ${formattedStartHour} completed:`, result);
        } catch (error) {
            console.error("Error aggregating hourly data:", error);
        }
    }

}
module.exports = startCronJobs; 
